Can’t get form total to update (A2003)

Topic

New Reply

I have a continuous form based on a query that lists (amongst other data) amounts and status, the latter in the form of a true/false tickbox. What I want to do is to have a total on the form that returns the value of the items ticked so that when I tick or untick an amount the total updates itself.

I’ve written a function to iterate through the database to arrive at the total of items ticked and have tried to attach this to the on click event for the tickbox. I can’t get this to work and I think this is because having not moved away from the record it hasn’t been saved. In other words the tickbox looks as if it has been ticked but (I’m presuming here) since the record is not actually saved the function is still reading the tickbox as unticked. I’ve also tried the after update event but this also seems not to work. Any ideas how I should proceed.

Regards

David

Reply | Quote

Replies

Whether you use an expression with Sum or with DSum or with a custom function, it will only look at the values as saved. You can’t expect an expression in the form header or footer to take unsaved values into account.

One workaround is to save the record in the After Update event of the check box:

Private Sub chkStatus_AfterUpdate()
RunCommand acCmdSaveRecord
End Sub

This will cause the sum to be updated (more or less) instantaneously.

Another workaround would be to treat the amount in the current record separately: set the Control Source of the text box to an expression like

=DSum(“curAmount”,”tblSomething”,”ysnStatus=True AND Not ID=” & [ID])-[ysnStatus]*[curAmount]

Here, tblSomething is the name of the table.
curAmount is the name of the amount field.
ysnStatus is the name of the status field.
ID is the name of the primary key field, assumed to be numeric.

DSum adds the amounts for all other records, and -[ysnStatus]*[curAmount] adds the amount for the current record if Status is True (the formula makes use of the fact that True = -1).

Reply | Quote

Thanks to both of you for the suggestions. The RunCommand did the trick. I’ll look further into your other workaround, Hans, as it seems to be more elegant than running through the whole table in VBA.

Regards

David

Reply | Quote

You can force a Save with either

DoCmd.RunCommand acCmdSaveRecord or me.Dirty = False

Reply | Quote