I have a subform in continuous display and I want to make a change on the mainform based on a change in the subform. When I make a change in a control on the subform, I want an aggregate sum in the main form to update. Let's call this control control1. I can get the value of control1 with Me!control1 and it is the updated value. Control1 is bound to field1 in the underlying recordset. When afterUpdate event on the control fires, I look at the Me!recordset.field1 in my code and its still the old value. So if I want to sum the field1 values of every record in the recordset, it will be wrong, it will contain the old value of control1 on the record I just updated. My solution is to set the field1 value to the control1 value and then sum it. Here's the code:
Code:
Private Sub quantity_AfterUpdate()
Me.Recordset.Edit
Me.Recordset!field1 = Me!control1
Me.Recordset.Update
Dim rs As Recordset
Set rs = Me.RecordsetClone
Dim sum As Long
sum = 0
With rs
If .RecordCount = 0 Then
returnTotal = sum <--let's pretend I didn't just cut and paste this code from elsewhere to make this code sample more obvious
Else
.MoveFirst
Do While Not .EOF
sum = sum + (!Price * Nz(!field1, 0))
.MoveNext
Loop
returnTotal = sum
End If
.Close
End With
End Sub
The problem with this solution is it's dog slow because access has to think about it for some time. Almost 2 seconds. It should just work without this first step. Does anyone have code that does this?