Populating reports with calculated values

I hope this is a simple question and you don't have to waste too much of you time on it. I have an report (called repRAD78) which contains a textbox (called txtRAD8). I would like to populate txtRAD8 with a calculated value based on numbers pulled from a query called qryrRAD78. Looking through the forums it looks like recordsets would be the solution but this is my first foray into recordsets and it's not going well. :( The code I have pasted in below I have pulled together from a number of places and it doesn't produce any errors but puts the same value into txtRAD8 for all the records. I'm sorry if this is a stupid question but it's been driving me potty. Many thanks for your time. Al.

Public Sub Calc()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryrRAD78")
rst.MoveFirst
Do Until rst.EOF = True


Dim lngMean As Long
Dim lngRAD78max As Long
Dim lngRAD78_1 As Long
Dim lngRAD78_2 As Long
Dim lngRAD78_3 As Long
Dim lngRAD7 As Long
Dim lngRAD8 As Long

lngRAD78_1 = rst![RAD78_1]
lngRAD78_2 = rst![RAD78_2]
lngRAD78_3 = rst![RAD78_3]
lngRAD8b_c = rst![RAD8b_c]

lngMean = (lngRAD78_1 + lngRAD78_2 + lngRAD78_3) / 3

lngRAD78max = Maximum(Abs(lngRAD78_1), Abs(lngRAD78_2), Abs(lngRAD78_3))

lngRAD7 = ((lngRAD78max - lngMean) / lngMean) * 100

lngRAD8 = ((lngMean - lngRAD8b_c) / lngRAD8b_c) * 100

txtRAD8.Value = lngRAD8

 rst.MoveNext
Loop

rst.Close
dbs.Close

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Calc
End Sub

Solution

Here's a second approach to this. Rather than using a function in the code, take the calculations from your Calc() routine and put them in another query.

SELECT idrRAD78,
    (RAD78_1 + RAD78_2 + RAD78_3) AS Mean,
    (IIf(Abs(RAD78_1) > Abs(RAD78_2),
        IIf(Abs(RAD78_1) > Abs(RAD78_3), RAD78_1, RAD78_3),
        IIf(Abs(RAD78_2) > Abs(RAD78_3), RAD78_2, RAD78_3))) AS RAD78Max,
    (((RAD78max - Mean) / Mean) * 100) AS RAD7,
    (((Mean - RAD8b_c) / RAD8b_c) * 100) AS RAD8
FROM qryrRAD78

This will give you a query that performs the same calculations as your existing function. Then just edit the report query to join to this new query (just like joining a table) using something like:

FROM ReportQuery INNER JOIN NewQuery ON ReportQuery.idrRAD78 = NewQuery.idrRAD78

Change the query names to match the real names. Add the fields from the new query in the SELECT part of your report query:

SELECT <existing field list>, RAD7, RAD8

Then set txtRAD8 to the RAD8 field.

I'm just doing this from memory as I'm not in front of my own computer, but hopefully that makes sense and is close enough to the correct code.