Results 1 to 5 of 5
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Totals off of a subform (A2003 SP2)

    Simply put, I have a form with a subform. One piece of data on the subform is units stored as double. There are many records in the subform so I need to put a summary textbox above the subform that shows the total units. This would be easy enough using DSum on Units on the data source of the subform if it was a query one could access. The problem is the Subform rowsource is changing on the fly based on users selections in Multiselect List Boxes and the strSql statement is constantly being updated.

    The question is can Dsum be used in the following manner: DSum("[Units]", strSql) where strSql is a variable that is constantly changing. If so, I can not figure out the syntax. Is this possible or is there another way to do this. I have written code that groups the data and updates a table so I can then use the Dsum but it takes too much time. Or is there a property on the subform that I can use that I am overlooking? Just looking for ideas here.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Totals off of a subform (A2003 SP2)

    DSum and related functions only accept the name of a table or stored query as second argument, not an SQL string.

    You could place a text box txtSum in the form footer of the subform with control source =Sum([Units]), and to refer to this text box in the control source of the summary text box on the main form:

    =[SubForm]![txtSum]

    where SubForm is the name of the subform as a control on the main form (this is not necessarily the same as the name of the subform in the database window). You can hide the text box on the subform if you wish.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Totals off of a subform (A2003 SP2)

    Thanks Hans. I will give this a try (Figured I should know this by now).

    I wonder, will this work if the subform is in Datasheet mode? I will have to try and see.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Totals off of a subform (A2003 SP2)

    Yes, it should work OK with a datasheet subform. A text box in the form footer of the subform will not be visible in datasheet view, but you can still refer to it.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Totals off of a subform (A2003 SP2)

    Thanks Again. Works great. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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