MS access 2003 - How can i trap for "#Error on a calculated field on a form

Posted on 2006-05-09
Last Modified: 2012-08-13
I have a main form with the field amount remaining.
On a subform1 I list bonds $ amounts and total them in a field called CompanyBondtotal
On another subform2 I list bond deductions and in a field on that form I sum up total deductions.

In the amount remaining field on the main form I have a formula that calculates the amount remaining using the formula
=[bbonds3].[Form]![CompanyBondTotal]-[bbonddeduct].[Form]![EncomberedTotal] on the control source of that field.

Everything works fine until there are no bond deduction records to subtract from the CompanyBondTotal .

If there are no records in subform2 my amount remaining field on the main form indicates #Error. How can I get the amount remaining field to indicate zero (0) when the formula above has no value in [bbonddeduct].[Form]![EncomberedTotal] part of the formula.

I have tried the following but cannot get it to work.





Any ideas would be greatly appreciated.

Question by:Lou Dufresne
    LVL 65

    Accepted Solution

    afaik you can't trap #error; you have to manually find the error and fix it.

    >when the formula above has no value in [bbonddeduct].[Form]![EncomberedTotal] part of the formula.
    Use the Nz(value, value if null) to trap for nulls, if this is your issue.

    Nz([bbonddeduct].[Form]![EncomberedTotal],0) = 0 if EncomberedTotal does not have a value
    LVL 58

    Assisted Solution

    Hello Ldufresne19

    Jim is right, you can't trap the #Error message display. There is a function called IsError(), but it doesn't work as expected.

    There is another problem: you should not get #Error in your case. This occurs when you are referring to a control on a form without any record (not even the new record) or if you have a spelling error in your function. If both CompanyBondTotal and EncomberedTotal are defined as "=Sum(<field name>)" and are both on the subform's header or footer, they should contains a number or Null, but not #Error.

    Make sure they are in the header or footer and try each alone first, e.g.:


    Hope this  helps!
    LVL 1

    Author Comment

    by:Lou Dufresne
    Thanks everyone. Both answers were very helpful.


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now