Form Level Code lower level not refreshing.

[Vincent]

I need to know is how to copy
data that is currently a total in an unbound calculated
field (Subtotal) to populate another field
(InvoiceAmount), they should always be equal.

They in the same table. The bottom line is I need this
total stored (eventhough NOT conventional).

Currently it works fine on the form level, using the
following code:

Me![InvoiceAmount] = Me![Subtotal]


I would like to know how to do this at a FORM level
because it doesn't refresh? EG. On Exit...????

Please helP!!!

PS Thanks Ronald for the lead, previous post if you see
this.

Vincent

 

[Allen Browne]

Assuming you do have a good reason for breaking the normalization rules, you
could do this in the AfterUpdate event procedure of your subform. That event
fires whenever there is an insert or edit in the subform. You would also
need to use the subform's AfterDelConfirm event to make the changes after a
deletion as well, and make sure the Confirm box is checked under Tools |
Options | Edit/Find | Record Changes.

To get the total, you will need to DSum() the amount from the subform's
table. Even if you do have a calculated control in the subform's Form Footer
section, it will not be up to date in time (and there is always the chance
that the subform may be filtered).

This kind of thing:

Private Sub Form_AfterUpdate
Dim strWhere As String
With Me.Parent
If Not .NewRecord Then
strWhere = "[MyForeignKeyID] = " & ![MyMainID]
!InvoiceAmount = Nz(DSum("[Qty] * [UnitPrice]", _
"MySubformTable", strWhere), 0)
.Dirty = False
End If
End With
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call Form_AfterUpdate
End If
End Sub