subform total on Main

[Guest]

I have read many threads here about the topic and basically have it working
with one big problem, the sum displayed is always one record behind. I have
used both LostFocus and AfterUpdate on the Amount field on the subform but
the result is the same. The subform has a text box called InvTotal whose
control is =Sum(Amount).

After entering a payment of 200 on the subform the PmtTotal on the main
still shows 0.00. Then I enter a second payment of 500 and at this point the
PmtTotal shows a value of 200. here is my code and I really appreciate
everyones help.

Private Sub Amount_LostFocus()
Dim lngAmt As Long
With Me.Parent.PmtTotal
.Enabled = True
End With
lngAmt = Nz(Forms!Frm_Payments!SubPmtControl.Form!InvTotal, 0)

Me.Parent.PmtTotal = lngAmt
Me.Parent.PmtTotal.Requery

End Sub

 

[Allen Browne]

The total doesn't get updated until you save the record.

You can force the record to be saved (and so the total to be updated) with:
Me.Dirty = False

 

[Guest]

Allen,

Thanks for the reply. I have tried adding the statment Me.Dirty = False
both before and after lngAmt =
Nz(Forms!Frm_Payments!SubPmtControl.Form!InvTotal, 0) however it does not
seem to do teh trick. Any other way I can make this work?


Thanks

 

[Allen Browne]

The other alternative is to craft a convoluted expression that sums the
values from the *other* rows in the form, plus the value of the current
row. That way the total reflects the value in the row being edited as soon
as you leave that control, even before the record is saved.

Is it worth the trouble?

If you already have an expected total you anticipate reaching once all
subform entries are there, another idea is to use the BeforeInsert event
procedure of the form to assign the amount remaining to the field where you
enter the amount. This does several things:
- It is the most likely amount to be entered, so it saves typing.
- Even when there are several amounts, it saves the user having to figure
out what the balance.
- It reduces entry errors, and especially the cases where the user may enter
too much.