Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum of Sum on Form (2000 SR1a)

    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!)

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of Sum on Form (2000 SR1a)

    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?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum of Sum on Form (2000 SR1a)

    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=" & <!t>[trans]<!/t>)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Sum of Sum on Form (2000 SR1a)

    I have corrected my reply. The Lounge software had eaten the <!t>[trans]<!/t> - 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>[t]<!/t> and <!t>[/t]<!/t> tags to avoid the problem.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •