Subform totals not working

[VBAPrincess]

I have an Access 2003 mdb that I created for a client at least 10 years ago. They've asked for some updates and now I'm trying to make changes to the forms. After I began making changes, and saved those changes, the totals at the bottom of the subform (in the form footer) stopped working properly. I then created an accdb file and imported all of the objects. The totals still didn't work so I recreated one of the subforms and that didn't help either. After racking my brain and trying all sorts of things, I finally decided to move on to other stuff. I tried a filter on the form and suddenly the totals began working. ??? However, the totals do not work when I am showing all data for a sales manager. The totals only work when I filter for an employee who works for the manager. The code applies a filter to the same query. It does not change the record source of the subform.

Here is a screen shot of it not working, and one of it working.

I'd appreciate any guidance! This is driving me batty!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[MajP]

how are those totals calculated: calculated control, dlookup, subquery...
My guess the there is some bad data such as nulls that your calculation cannot handle. This could answer why the filtered set (no bad data) works and the unfiltered does not.

 

[VBAPrincess]

Thanks MajP! The totals are calculated controls -- each box has a formula like =SUM([PriorYrTotal]).

I finally got it working! I tried using NZ to force a zero but it didn't seem to work. I then recreated each subform in 2010, and included the nz function, and then it worked on all but one subform. I'm still trying to figure out why that one still shows #Error, except when I filter by an employee. I've saved a working subform as the one I need, changed the record source to the desired query (which is only different from the other because it filters by a different account type), and tested but still an error. I'll look at it again on another day and hopefully the difference between this one and the others will be easy to spot.

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!