DSum on Change (2003)

Topic

New Reply

I have a subform in a form, where the users enter weights for alloy. I have put a control on the subform that gives me a running total of alloy used, using

=DSum(“[Alloy Weight]”,”tblDetails”,”[ID] <= Forms![frmLog]![ID]").

The problem I have, is that I have been asked for the totals to change, when the Alloy changes eg

Alloy Alloy Wt Total
C111 23.00 23.00
C111 15.00 38.00
C111 10.60 48.60
C112 17.00 17.00

I the only way the form and subform are linked is by the ID, so I don't know how to achieve the above.

Reply | Quote

Replies

Couldn’t you just run a requery after the update of the alloy field?

Reply | Quote

I’m not sure I understand. Re-query what?

Reply | Quote

In the after update event of the alloy field, enter the command docmd.requery “Forms!Mainform!SubForm!Total – or whatever the reference is to the running total you are trying to update

Reply | Quote

What do ID and Alloy have to do with each other?

Reply | Quote

The ID on the Main form and ID on the subform are linked. The idea of the form is like a log book. The user enters his name on the Main form, and details are entered on the Subform, so we can have multiple entries on one day. ID and Alloy are not linked, that is my problem.

Reply | Quote

It would be easier to do this in a (sub)report grouped by Alloy. You could set the Running Sum property of the total text box to Over Groups.
Forms aren’t really suitable for this.

Reply | Quote

Thanks Hans. Looks like that’s what I’ll have to do. The users have to fill in a card of how much of the alloys they have used during the shift. I suppose, I could put a button on the form to run the report for just that period.

Reply | Quote