How to have a total field updated

[Marclem]

Hi all

I created the following Update query to a field called Total_used and placed it on my form called INVENTORY

DSum("[Mon]+[Tue]+[Wed]+[Thur]+[Fri]","INVENTORY","[InventoryID] = " & [InventoryID])

All I want to do is: when I change the amount from any of those weekdays fields, my Total_Used field changes its value on the form.

I inserted the macro called WEEK SUM that opens this query and runs it under on current, after update of the form but did not get any results.

Thanks for your support!

 

[BobJacksonNCI]

Just a guess, and based on my last contribution confidence is LOW...

In the macro, make sure warnings are on until you have it working.

I assume InventoryID is numeric - if it's text you need to add quotes.

If any of the fields are null, the whole thing is discarded.

If that's the case, this should help:

DSum("nz([Mon]) + nz([Tue]) etc

HTH,
Bob

 

[tpolony]

Try Total_Used.Requery in the field's AFTERUPDATE method.

 

[Marclem]

I put the DSum("nz([Mon]) + nz([Tue]) etc and Total_Used.Requery still it does not change the total_used field when I change any of my weekdays fields.

My Inventory ID is numeric

 

[BobJacksonNCI]

Hmmmm,

Add After Update events for each weekday field using the macro.

Seems like your approach should work, but you said you want the total to update immediately following a change to any of the weekday fields.

This should do that.
Fingers crossed,
Bob
P.S. Leave in the nz code as you never know when one of the fields will be unused - like Labor Day.

 

[Marclem]

Bob Thanks for your help, I searched a little bit more and found out that a made a mistake on my macro having it running showing through design mode instead of datasheet view on which runs the macro automatically.

I did put the macro under each weekdays on the After Update, but it does not change my Total_used field. The only time it changes is when I go to different record, so when I come back to it, the data is changed on my total field.

 

[BobJacksonNCI]

You're welcome.
Hopefully, persistence will pay off...

In the macro, after the dsum add a requery command.

HTH,
Bob