Summing all cells of a datasheet form

[priapus]

I have this subform which consists of a combo box (based on a query) and another text box (Amount). On my form, the user can choose a billingOptionID from the subform's combo box and the amount for that option shows up in the field next to it. The subform is in datasheet view, because there are a couple of billingoptions he can have, so the user can click on an empty row and add another billingoption. Well on the form I have a text box where I want to reference the total amount of all his options.
Control Source=[optionsSubform].Form![amount] only gives the current/most recent row's Amount. How do I sum them all? (No, simply sticking a Sum() around it doesn't work). Thanks in advance to y'all smarter than me who looked at this.

 

[AccessSQLUser]

What if you would put the textbox in the form footer with the controlsource:
sum([optionsSubform].Form![amount])

 

[priapus]

Nope, same error as before with the Sum statement. Thanks for takin the time to look tho.

 

[AccessSQLUser]

You get an actual error it just doesn't work?

 

[priapus]

Yup, #error. But without the Sum() I get a valid value, but just one cell, not the total sum.

 

[JoeMiller]

Rather than summing the data off of the form, use a DSum to sum the data from the query providing data to the subform like so:

=DSum("[MyField]","MyQuery","[MyID]=[MyID]&quot

Then in your subform, make sure that you put code to refresh the control holding the DSum statement so that when you add/delete/edit records it refreshes the total to reflect the changes like so:

[Forms]![MyForm]![MyDSumControl].Requery

HTH Joe Miller
joe.miller@flotech.net

 

[priapus]

Ah, that did it. Thanks Joe.