Updating Calculated field with dsum

[Martyh]

how do I programmatically update a calculated field with dsum in it?

the quote from ms is "Unsaved changes to records in domain aren't included when you use this function. If you want the DSum function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method."

I can't seem to update the field from an AfterUpdate event on another control. Have I got the proper event?

Also what "Update method" are they talking about? Can't find anything on it!

TIA

 

[RuralGuy]

What is the ControlSource of the control you are trying to update? What code are you using in the AfterUpdate event of the other control?

 

[Martyh]

Hi RG,

the only bit of code is:

Private Sub trxPlanAmt_AfterUpdate()
Me.varPlanned.Requery
End Sub

This is for a text box on the screen. After the update to that box, I'm trying to requery varPlanned which is a calculated text box with =DSum("[trxPlanAmt]","tblTransactions","[relBudID] = " & [BudID]).

But there must be something else that I'm still missing.

 

[RuralGuy]

Now to answer some of your original questions. The Domain functions like DSum() open a new copy of the table/query (their Domain) and as a result they can only see saved records. It looks like your TextBox is bound to the same query/table your DSum() function has as a Domain. The AfterUpdate event of a TextBox is for the TextBox control but not the form so that data has not been saved yet and your DSum() can not see it. You would need to force the form to save the current record in order for the DSum() to include the new data in the sum. You can do that with the following line:
If Me.Dirty Then Me.Dirty = False
in the AfterUpdate event of the TextBox before the current code. Speaking of the current code, I believe you need Me.varPlanned.Recalc rather than Requery.

 

[Martyh]

RG,

That is BEAUTIFUL -- You solved the problem. THANKS!!

PS
varPlanned.Requery seems to work --- and recalc provides an error.

 

[RuralGuy]

Great news. I don't use the Domain functions much so I'll file the Requery info from you away in the old noggin. Thanks.