none
Using Dsum for currnet record on subform RRS feed

  • Question

  • Hi all

    I create one subform (Continuous Forms) with control source from below table (for example). I am using one textbox to calculate Sum in field 2 with condition is set from field1. Suppose that i am inputting data on subform for line 5 on 02 Jun 18 that link from mainform. how i can sum for "A" condition on current record. the result is 10. i already searched the help but no got the result. Please help me in this case. I wanna display this result on mainform when data is updated by user. thank you in advance.

    ID Field1 Field2 Date Line
    1 A 4 02-Jun-18 5
    2 B 3 02-Jun-18 5
    3 A 6 02-Jun-18 5
    4 B 7 02-Jun-18 5
    5 C 9 06-Jun-18 4
    6 C 10 06-Jun-18 4
    7 D 11 06-Jun-18 4
    8 D 12 06-Jun-18 4


    Wednesday, June 6, 2018 4:08 PM

Answers

  • Hi guy

    can you open form FrmData. You can see all records on subform that i call them is current record. the record can add more. everytime add one record I want to sum one of product type. Then it will display on mainform.


    If i sum product type "AA", the result is 15. If i add one more AA with number is 5. immediately the sum is changed 20.

    Hi,

    Thank you for the clear instructions. Here's what I did.

    And here's the result:

    Hope it helps...

    • Marked as answer by Nghi Trinh Thursday, June 7, 2018 4:03 PM
    Thursday, June 7, 2018 3:36 PM

All replies

  • Hi,

    Just a guess but maybe something like:

    DSum("Field2","TableName","Field1='a' AND ID<" & [ID])

    In other words, sum all A records with ID value less than the current ID.

    Hope it helps...

    Wednesday, June 6, 2018 4:18 PM
  • Hi

    It is not work with current record on subform. may be we use other way with recordset. Using while and loop with specific condition is "A", or "B" and whatever. But i still find the best solution.

    Thursday, June 7, 2018 1:10 AM
  • Hi,

    Where do you want to see the Sum? How exactly did you try using DSum()?

    Thursday, June 7, 2018 1:44 AM
  • Hi.

    I want to see the Sum on mainform. But it should be calculated on subform first. If we use  while ...Loop with current recordset on subform, it may be no need to use Dsum. But so far i dont know how to write VBA code exactly

    Thursday, June 7, 2018 2:34 AM
  • Hi Nghi,

    Could you share us a simple Access database with current form and data, and share us the expected form with the specific condition, we will try to work on your provided database.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 7, 2018 6:20 AM
  • Hi.

    I want to see the Sum on mainform. But it should be calculated on subform first. If we use  while ...Loop with current recordset on subform, it may be no need to use Dsum. But so far i dont know how to write VBA code exactly

    Hi,

    So, when you're on the "current" record on the subform, are you saying you are entering a "new" record?

    Or, are you saying you want to sum the matching records even if you were "editing" an existing record?

    Also, can you please post the SQL statement for your subform?

    To use a recordset based on the subform, you can use the RecodsetClone method. For example:

    Dim rs As Object
    
    Set rs = Me.RecordsetClone
    

    Just my 2 cents...

    Thursday, June 7, 2018 3:02 PM
  • Hi ALL

    Here is my simple database with formdata. I hope I can sum all "AA" Product Types on current record

    Click here

    https://drive.google.com/open?id=1hcdfquoBUzdil3FsVdRBsZL2tgHKLg5D



    when new record is update with same line and date that mean it display on subform. The Sum is auto runs. I can not find solution now
    • Edited by Nghi Trinh Thursday, June 7, 2018 3:10 PM add some explain
    Thursday, June 7, 2018 3:05 PM
  • Hi ALL

    Here is my simple database with formdata. I hope I can sum all "AA" Product Types on current record

    Click here

    https://drive.google.com/open?id=1hcdfquoBUzdil3FsVdRBsZL2tgHKLg5D


    Hi,

    I downloaded your file but I am still confused about your reference to the "current record."

    Can you give me a step-by-step instruction on what to do? Thanks.

    Thursday, June 7, 2018 3:10 PM
  • Hi guy

    can you open form FrmData. You can see all records on subform that i call them is current record. the record can add more. everytime add one record I want to sum one of product type. Then it will display on mainform.


    If i sum product type "AA", the result is 15. If i add one more AA with number is 5. immediately the sum is changed 20.
    • Edited by Nghi Trinh Thursday, June 7, 2018 3:26 PM more info
    Thursday, June 7, 2018 3:23 PM
  • Hi guy

    can you open form FrmData. You can see all records on subform that i call them is current record. the record can add more. everytime add one record I want to sum one of product type. Then it will display on mainform.


    If i sum product type "AA", the result is 15. If i add one more AA with number is 5. immediately the sum is changed 20.

    Hi,

    Thank you for the clear instructions. Here's what I did.

    And here's the result:

    Hope it helps...

    • Marked as answer by Nghi Trinh Thursday, June 7, 2018 4:03 PM
    Thursday, June 7, 2018 3:36 PM
  • Hi Guy

    It is too easy. i just think it must quite complicate. thank you so much. i will try

    Thursday, June 7, 2018 3:53 PM
  • Hi Guy

    It is too easy. i just think it must quite complicate. thank you so much. i will try


    You're welcome. Let us know how it goes.
    Thursday, June 7, 2018 3:54 PM
  • Hi Guy

    It is perfect. the access world is wonderful when it has many kind guys like you. Thank you so much

    Thursday, June 7, 2018 4:07 PM
  • Hi Guy

    It is perfect. the access world is wonderful when it has many kind guys like you. Thank you so much

    Hi,

    Glad to hear you got it to work. We're happy to assist. Good luck with your project.

    Thursday, June 7, 2018 4:08 PM