1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Adding numbers in a subform (Access 2000)

Discussion in 'Business Applications' started by DKTaber, Nov 15, 2001.

Thread Status:
Not open for further replies.
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    About a year ago, I created an input form that contains a subform in which are listed all the contributions of a member of the organization. Under the subform -- IN THE MAIN FORM, not within the subform -- I had placed a field that summed the contributions for that individual, and another field that displayed the domain sum (DSum) for all contributions. Both have worked perfectly every since.

    The field that sums the contributions for the individual used the formula "=[Contributions subform].[Form]![ContribSum]". The field that does the domain sum is "=DSum("Contribution","Contributions")+IIf(DSum("MatchAmt","Contributions") Is Not Null,DSum("MatchAmt","Contributions"),0). I removed the field "MatchAmt" from the table, and changed the DSum statement to just "DSum ("Contribution","Contributions").

    After doing that, both field yield only "#Error". Part of my puzzlement is the field "ContribSum". It is not a field in either table, and is nowhere in either form. I haven't the foggiest where it is or why the formula refers to it. I tried using just "=Sum([Contributions.subform].Form![Contribution]), but that for some reason reports only the first contribution; doesn't add all of them up.

    So my question is, how does one sum number fields in a subform in a field OUTSIDE of the subform/in the host form?

    Help!
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Put the "=" back in front of DSum, so that it says this:
    Code:
    =DSum("Contribution","Contributions")
    The "#Error" means that Access is trying to do some sort of operation that it can't pull off.

    As for your sum question, it's cagey, unless you can program a little. The workaround is this:
    1. In the subform, show the form footer. There should be a control there called ContribSub, I'm guessing, from your post. If not, make one.
    2. Set its control source to
      Code:
      =Sum([Contribution])
    3. Once you're sure the subform sum is working, you can set the Visible property of the form footer to false. That way you don't see your total down there.
    4. Now you can build a control source on the master form whose control source is set to
      Code:
      =[Contributions subform].[Form]![ContribSum]
    As for why your other attempt wasn't working (the one that goes like "=Sum([Contributions.subform].Form![Contribution])"), you must remember that Access maintains only one "active" record at a time, per form or subform. So your control wasn't lying; I'll bet changing records in the subform would prove that. But it's only adding the total of the active record--this is the big difference from a report, where there are no active records.

    Hope this helps. Let us know.
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Many thanks for the help, "downwitchyobadself". Problem solved. I found the "missing" field, [ContribSum]. I thought I had made it invisible, but I hadn't. I had instead reduced it to a line (essentially 0" in height) up against the top of the Form Footer, so it was not visible when you looked at the form. I knew it was there because when I tried to create another field in the Footer, and named it the same thing, Access squawked, saying the field already existed. So I hit Edit, Select All to see where all the fields were, and there it was...little border handles right up against the Footer section bar. I pulled the field open, and the formula still contained the fields I had deleted, so naturally it didn't work. I took those fields out, and voila!, it works again.

    I have used Access for about 5 years now, and am sufficiently proficient with it that I write applications (some quite complicated) for non-profit organizations. I should have been able to figure this out EASILY, but I really do think the older I get and the more experience I have with Access, the dumber I get. Either that or somebody's putting something in my coffee! In any case, I apologize for cluttering this terrific forum with junk, and for taking up your time for something so basic.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/58684

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice