Sum of Sum on Form (2000 SR1a)

Topic

New Reply

I have a form with a subform and a sub-subform. I would like to have a running total of all of the amounts for each record of the main form. These are transaction sheets recording membership income for a club and the paper records are not normalized. “Line” refers to the line number on the sheet and each line can have more than one amount, e.g. dues and a T shirt purchase. I need a running sum for the whole transaction sheet, not just the current detail record. Attached is a quick and dirty version of the way it is set up. I tried using DSum with a saved query, but I can’t get it to Requery and refresh the total in the Grand Total on the fly. This version is just using Sum and trying to Sum the Sums (if that isn’t too confusing!)

Reply | Quote

Replies

Create a query based on the three tables, and add the trans and Amount fields to the query grid. this query lists all trans and Amount values. Save it as qryList. Set the Control Source of the text box on the main form to

=DSum(“Amount”,”qryList”,”trans=” & [trans])

To ensure that the text box will be updated whenever an amount is changed, use this code for the After Update event of the Amount text box on the second subform (it’s not a sub-subform, by the way):

Private Sub Amount_AfterUpdate()
Me.Parent.Recalc
End Sub

Reply | Quote

Thanks Hans. This looks as straightforward as I figured it should be. I was not aware of the Recalc function. Is there, perhaps something missing after the ampersand in your DSum function? A reference like Me.trans?

Reply | Quote

I have corrected my reply. The Lounge software had eaten the [trans] – it considers anything between square brackets that begins with tr to be an HTML tag for a table row. (I replaced the square brackets [ and ] with the [t] and [/t] tags to avoid the problem.)

Reply | Quote