Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sum on a continuous form (2002)

    Hi,

    I have frmSalesDataEntry which is bound to tblImport and is a continuous form. The user enters net_sales by Service_date. Each entry is assigned a number which comes from and unbound text box caled txtStoreNo. All of this works fine. The user would now like to be able see a running total for the net_sales as it is entered. I set an unbound textbox called txtNetSales whose data = sum([net_sales]). This works fine when there is only one txtStoreNo. However, when a new txtStoreNo is entered, the txtNetSales continues to sum all the records in thet table, not just the net_sales for txtStoreNo. Is there a way to make txtNetSales only show the sum for the number in txtStoreNo.

    Thanks,
    Leesha

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

    Re: Sum on a continuous form (2002)

    Try an expression of the form

    =DSum("net_sales","tblImport","[numberfield]=" & [txtStoreNo])

    where numberfield is the name of the number field. You will have to put an instruction Me.Recalc in the After Update event of txtStoreNo and in the After Update event of the form.

    I have assumed that numberfield is numeric. If it is a text field, use

    =DSum("net_sales","tblImport","[numberfield]=" & Chr(34) & [txtStoreNo] & Chr(34))

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum on a continuous form (2002)

    Hi Hans,

    Thanks for the reply and explanation. I keep getting an error message in the field on the form. I'm not quite sure what [numberfield] is supposed to be and tried every combination without any luck. I did notice that I spelled the table name wrong in my post - it is tbImport so I made that change but still get the error message. I'm attaching a stripped down version so you can point me in the right direction.

    Thanks!
    Leesha

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

    Re: Sum on a continuous form (2002)

    Try this:
    <code>
    =DSum("Net_Sales","tblmport","[Store_ID]=" & Nz([txtStoreNo],0))
    </code>
    The text box you want to refer to is txtStoreNo, not txtNetSales (that is the text box in which you want to display the sum). The text box txtStoreNo is unbound, but supplies the value for the field Store_ID. So the where-condition becomes "Store_ID = " & [txtStoreNo]. To avoid problems when txtStoreNo is blank, I have added the Nz function.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum on a continuous form (2002)

    That is perfect and I love the nz piece! As always thanks so much for the explanations!!

    Leesha

Posting Permissions

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