By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

Running Total Returns Error

sbouley7
P: 6
I've been working with VBA in Access for a while and with the help of Google can figure out most things on my own but this one has got me stumped. I've created running totals in the past with no problem but keep getting #Error in the main form total field. I'm sure it's something really simple but have been working for two days.

Thanks in advance!!!

Here's what I have:

I have a main form with a sub-form in datasheet view.

The main form is used to add donations by person and category to a details table and populates the sub-form.

The records in the sub-form contains a series of categorized dollar values with a calculated field at the end of each row called txt_rec_tot that adds all of the dollar values for each record to get a record total. The Control Source for txt_rec is:

Expand|Select|Wrap|Line Numbers
  1. =NZ([Cat_1],0)+ NZ([Cat_2],0)+ NZ([Cat_3],0)
I added a text box to the Form Footer called txt_running_tot to keep a running total of the calculated field for each record with the following Control Source:

Expand|Select|Wrap|Line Numbers
  1. =Sum([sfrm_Donation_Details].[Form]![txt_rec_tot])
Finally, there is another text box on the main form called txt_Total that pulls the value from txt_running_total and displays the running total. The control source is:

Expand|Select|Wrap|Line Numbers
  1. =[sfrm_Donations_Details].[Form]![txt_running_total]
As mentioned earlier, this process returns #Error.

The format for all three text boxes is currency,

Thanks again
Jun 9 '14 #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
sbouley7,

Do a quick double-check of the names of your Text Boxes. Above, (it could just be a typo), but it looks like you are talking about a Text Box names "txt_running_tot", but in the Code you supplied, it is called "txt_running_total".

Aside from this, doing totals on a Form can sometimes be tricky, so I have a d=tendency to have a calculated field in which the record source actually calculates a sum using DSum, or something similar.

What I think is the cause here is that you may be confusing some of the functions and features of reports with Forms. When you have a grouping on a Report, each grouping can sum totals across that group very easily. However, with Forms (and especially subforms), the data is not looked at the same way, as the subform is a subform, not a grouping of a set of records (it is, but the db can't look at the data the same way).

I don't know exactly how your forms are set up, but I would recommend calculating totals in a different way for your forms.
Jun 9 '14 #2

sbouley7
P: 6
Twinnyfo,

Thanks for your reply. The fieldname mismatch was a typo.

I try to avoid using domain aggregate functions when they're not necessary as they tend to slow the form down when there's a lot of data to go through. I already have the running total working with a DSUM for each donation type and a calculated field to add them together but if there's a way to get the running total working using calculated fields I would prefer to do it that way.

Thanks again.
Jun 9 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,483
sbouley7,

Onething I have learned over the years is that Access doesn't like to Sum calculated fields on a Form. If you were to test this, try creating three new running sum fields:

Expand|Select|Wrap|Line Numbers
  1. =Sum([Cat_1])
  2. =Sum([Cat_2])
  3. =Sum([Cat_3])
And those Sums should work. Then, your Calculated Field could be:

Expand|Select|Wrap|Line Numbers
  1. =txt_Cat_1_tot + txt_Cat_2_tot + txt_Cat_3_tot
I've been able to duplicate your error, and my experience has been the the inability for Access to Sum values in this way.

Hope this hepps!
Jun 10 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.