After update event calculations on subform

[Harmannus]

Hallo,

I have a employee form with a subform containing cash register amounts (key
is employeeID). How can automatically calculate a total amount in the after
update event of that subform through a update query (see code below): now i
get a error when goning to the next employee: i get a messages asking for
the cashregisterID.

fsubCashregister after update event:

Private Sub Form_AfterUpdate()

Dim stDocName As String
stDocName = "qupdAmount"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

in the query qupdAmount i calculate the total amount field based on

[Forms]![fsubCashregister]![CashregisterID]

Any suggestion on how to correct this?


Regards,

Harmannus

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

 

[Harmannus]

Hallo Allen,

Thanx for the response!

I will keep this rule in mind ;-) & follow your suggestion.

Thanx!

Greetz, Harmannus

This is a timing issue. The calculated field has not been updated at the
time the Form_AfterUpdate event runs, since updating a calculated control is
a lower priority task in Access. You could use a Totals query or a DSum() to
write the total.

A better idea would be not to store the total at all. Storing it only
creates headaches trying to guarantee it is always up to date. Instead, use
a query to calculate the total when you need it, and don't store it at all.
This advice is in keeping the fundamental rule of data normalization: don't
store dependent data!

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I have a employee form with a subform containing cash register amounts (key
is employeeID). How can automatically calculate a total amount in the after
update event of that subform through a update query (see code below):

now

i
get a error when goning to the next employee: i get a messages asking for
the cashregisterID.

fsubCashregister after update event:

Private Sub Form_AfterUpdate()

Dim stDocName As String
stDocName = "qupdAmount"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

in the query qupdAmount i calculate the total amount field based on

[Forms]![fsubCashregister]![CashregisterID]

Any suggestion on how to correct this?