Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubTotal #Error (2003) TIP

    This posting is to provide a hint, not to ask for help. I have spent hours working this issue and finally found the cause. This is not mentioned in any other postings that I could find here.

    HISTORY:
    I had made a sub form fsubOrderDetails with fields for Line/Part/Description/Qty/Cost/ExtendedCost bound to same fields in tblOrderDetails
    In the Footer I added two text fields txtLineCount and txtSubTotal with formulas to get Count(Lines) and Sum(ExtendedCost) for reference by frmOrders.
    Adding records, and browsing all seemed to be OK.

    Some time later, I used a utlity to rename the fields to add "txt" as a prefix to the fields. The formulas in the Footer also changed, referencing the newly named fields to txtLine and txtExtendedCost.

    PROBLEM:
    Some time later, again, I soon noticed that the Count and SubTotal were now both coming up with #ERROR.
    THIS IS THE PROBLEM I WAS TRYING TO FIX

    I thought, hmmm, maybe I need to use Sum(ExtendedCost) vs. the new Sum(txtExtendedCost). Made that change, opened the form and still got #ERROR in both fields.
    Maybe that was not correct, so changed back to Sum(txtExtendedCost) and decided to try same thing with Count.
    Changed Count(txtLine) to Count(Line), opened the form, and still got #ERROR in both fields. What is wrong?

    I will skip the details of scouring the forum postings, comparing to Northwind that looked exactly the same, with no success. Did a Compact/Repair process, no change.
    Finally added a new test form with only the 4 or 5 fields that I needed, and it worked fine, just like my original form.

    SOLUTION:
    Playing with this I found 2 things.
    1. The Sum/Count needs to refer to the field name that the form field is bound to. So, if txtLine was bound to Line, then I needed Count(Line), not Count(txtLine).
    2. If you have multiple "summary" fields in the footer, IF ANY ONE OF THEM IS WRONG, then ALL WILL RESULT IN #ERROR. You can have 3 of 4 fields with correct formula, only one bad formula, but running the form will look like all 4 have problems. Very misleading, which one to work on?

    When I was doing my troubleshooting, I had actually added another 3 or 4 "summary" fields in the footer with different formulas, searching for the answer. But it turns out that I would always get #ERROR on all of them if only one was incorrect.

    The final solution was to remove all the test fields, change the formulas in the original two fields to use the bound field name vs.the form field name. The naming to the bound field name was not a surprise, but the fact that one bad formula resulted in ERRORs in all the fields was a major surprise.

    TIP:
    So, to prevent others from falling into this trap, I will repeat the two lessons learned about "summary" field in a sub form footer:
    1. The Sum/Count needs to refer to the field name that the form field is bound to. So, if txtLine was bound to Line, then I needed Count(Line), not Count(txtLine).
    2. If you have multiple "summary" fields, IF ANY ONE OF THEM IS WRONG, THEN ALL WILL RESULT IN #ERROR.

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

    Re: SubTotal #Error (2003) TIP

    That's a good point. It has been mentioned before, for example in <post:=512,549>post 512,549</post:>, but it's useful to keep users aware of it.

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubTotal #Error (2003) TIP

    Thanks for that reference Hans.

    That is what i first tried to do as a fix, but it was complicated by the fact that I had multiple controls that were wrong. So when I corrected the initial problem I was tricked into thinking that was not the solution, and I ended up undoing the correct fix.

    Have you ever seen mention about multiple controls showing #ERROR when only one of them was invalid? That was a major surprise to me.

    Thank you again for your continued support and suggestion.

Posting Permissions

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