Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Create Running Total on a Sub Form not in the footer

    I have a subform that I would like to add a calculated control that provides the running total before going to the next record. This is what I have tried.

    =DSum("NetthisEntry","tblIncome_expenditure","batc hid=forms!frmaddnewbatch!batchid")
    This updates when I move to the next record not what I want
    or

    =DSum("NetthisEntry","tblIncome_expenditure","batc hid=forms!frmaddnewbatch!batchid")+[NetThisEntry]
    I thought this may work as it adds the current net for the entry.

    TRied building a separate form thinking that if I could force a requery that might work. Could not get that to work at all from would not come to the front no matter how I tried to set the focus and in any event did not requery.

    Appreciate that I can put a sum in the footer in fact I have one but I want to know the running total before moving to the next record on the sub from.

    Thanks

    Peter

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    When you try to do a running total in a query, you need to tell the system to only consider records on/before the current record. So, where did you do that in your use of DSum? You didn't.

    Logically you'd probably want to say something like "Sum all transactions that occurred on/before the date of the current transaction".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear

    you must save current record ( for this you may write a save command line)
    then you may run query =DSum(" ", " ", " ")
    See following example

    Function btotal()

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Dim am As Double

    am = Round(DSum("amount", "billdet", "[ID] = " & Forms!dbill!BillID), 2)
    Forms!dbill!DBillDets!SAmount = am
    End Function

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Instead of:
    =DSum("NetthisEntry","tblIncome_expenditure","batc hid=forms!frmaddnewbatch!batchid")+[NetThisEntry]

    Try the following if Batc hid is numeric:
    =DSum("NetthisEntry","tblIncome_expenditure","[batc hid]=" & forms!frmaddnewbatch!batchid)+[NetThisEntry]

    nb. I have enclosed Batc hid in brackets because of the embedded space.

Posting Permissions

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