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
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