calculated total field on continuous form duplicating on each row 1

[brendanmc]

Hi,

I have a continuous form which displays each record in a table. The form displays the records OK, but each row also has a total calculated field. The calculated total field works OK for one row but the total repeats itsself for every row instead of recalculating for each row.

The calculated field is unbound and uses a VB procedure to determine the value.

e.g. The form looks like this

name value1 value2 value3 value4 total
fred 1 1 2 5 31
bill 7 7 6 7 31
joan 6 6 9 6 31

It should be different for each row. e.g:
name value1 value2 value3 value4 total
fred 1 1 2 5 31
bill 7 7 6 7 25
joan 6 6 9 6 43

The form has VB which calculates the total as a combination of values of the form recordsource and another table.

The VB code is attached. I tried moving the Me!caseweighting stement outside of the loop to no avail.

Thanks
Brendan


Private Sub AssignWeighting()

Dim vAcute As Integer, vFGain As Integer, vIExtended As Integer, vMaint As Integer, vCaseW As Integer
Dim rs As DAO.Recordset
Dim CaseCalc As Integer
Set rs = CurrentDb.OpenRecordset("focusofcare")

rs.MoveFirst
Do While Not rs.EOF

If rs!carefocus = "ac" Then
vAcute = rs!weighting
End If
If rs!carefocus = "fg" Then
vFGain = rs!weighting
End If
If rs!carefocus = "ie" Then
vIExtended = rs!weighting
End If
If rs!carefocus = "ma" Then
vMaint = rs!weighting
End If

'Determine total weighting
CaseCalc = ((Me!AClients * vAcute) + (Me!FGClients * vFGain) + (Me!MClients * vMaint) + (Me!IEClients * vIExtended))
MsgBox CaseCalc & " " & vAcute
Me!CaseWeighting = CaseCalc
MsgBox "case weight :" & Me!CaseWeighting

rs.MoveNext
Loop
Close

 

[PHV]

An unbound control in the detail section of a continuous form has the same value for each record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[brendanmc]

Thats interesting, does that mean I can not get around the problem of the recurring value without major redesign of the form or record?

Maybe I could do a query which calculates the value as an extra field and use that as the query record source? - which should hopefully give me the value. (I'm trying avoid having a calculated value as a table value)

Thanks
brendan