Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSum ? - Simple I bet! (Access 2k, Win 2K Pro)

    I've been searching the lounge, but I thought I'd go ahead and ask anyway... On a form/subform, I have a link to the subform by AccountID to display, in the subform, payments. I am trying to setup a DSum on the main form to add the payments made so far to a given account ID. I can set up a combo box to search by name and set the DSum to work with the criteria [Person]=[Combo13] which is not good as I want to use the actual unique identifier rather than a name. I have since added a textbox (which can be hidden) to display the AccountID on the main form to use as a link to add the payments in the DSUM, but I am not getting the result I want. Very likely it's a syntax issue, but currently if I use the following:
    <font face="Georgia">
    =[TotalDue]-DSum("[Amount]","qryPayments","[AccountID]=" & [AccountID])
    </font face=georgia>
    for the control source, I get all the payments added, not the ones showing in the subform.

    The form/subform is using AccountID as the Child and Master link fields, so it is behaving as expected; the DSUM is -- I guess you could say -- over-enthusiastic...

    What am I doing wrong?

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

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

    Re: DSum ? - Simple I bet! (Access 2k, Win 2K Pro)

    You can put a (hidden) text box txtSum somewhere on the subform with control source
    =Sum([Amount])
    and put a text box on the main form with control source
    =[TotalDue]-[SubformName]![txtSum]
    where SubformName is the name of the subform as a control on the main form (this may be different from the name of the subform in the database window)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum ? - Simple I bet! (Access 2k, Win 2K Pro)

    thanks. i figured out a way to do this using DSum, but I understand domain aggregates can be slow (and they are a somewhat difficult to work with), but right now it is working with the way I cobbled things together.

Posting Permissions

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