Adding numbers in a subform (Access 2000)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top