• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Hide #Error on a form

Hi Experts,

I originally read an answer to this on EE but have looked for hours now and can't find it...sorry for the repeat!

I have a form which gives a conditional total (i.e. no of products a client ordered, once you choose a client).   Before the client is selected the field shows #error - as expected.   Someone posted some code which changed the font colour to white until a real value was shown - which worked nicely in forms and reports, if anyone can do this that would be peachy.

  • 4
  • 3
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  I don't know about the font color, but you can use the Function below.  In your control source, do:


  Note there is a Null to 0 call in there which you can remove if you don't want it.


Function AvoidError(n As Variant)
    On Error GoTo Trap
    AvoidError = NZ(n,0)
    Exit Function

    AvoidError = 0
    Resume Next

End Function
End Function
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  Oh one other thing: if your reference contains an domain function (SUM, AVG, etc), Access has a bug which causes any procedure call to be ignored.  The workaround for that is to make the original control hidden and then create a second control with the avoiderror call as the control source which references the first control. ie.

New Control control source:

Original control control source:

  Also I created a version of AvoidError that accepted a second parameter for the replacement value instead of assuming 0, but I can't find it at the moment.  It's handy when you want a space instead of the 0.  That's easy to add in though.

Jeffrey CoachmanMIS LiasonCommented:

Instead of "Masking" the error, how about setting things up so the error does not occur in the first place?
Like incorporating the NZ() function in your formulas.
See here:
(Yes, I do know that there may be lot's of other reason you might get the #Error, but I felt I should at least mention it)
JDettman can help you with that as well

And again, yes I do know that sometimes hiding it is just faster.
Again,... again, I just thought I should mention it

Good Luck


BTW her is a search here of "#Error"
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

what is error? Can you write error#
Jeffrey CoachmanMIS LiasonCommented:

The error is:  #Error

#Error is what shows up in the textbox if Access cannot perform the calculation.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<#Error is what shows up in the textbox if Access cannot perform the calculation.>>

  It also displays #Error if a recordset is empty even though a calaculation may be valid.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  BTW, I think were saying the same thing basically, but I just wanted to make the point that a calculation may be valid, but the data that feeds it may not.  It can also be a case that a calculation (ie. a reference in it) is invalid.

Jeffrey CoachmanMIS LiasonCommented:

<I think were saying the same thing basically,>
Yep, I think we are too.

I had not considered you reason, mainly because I usually get this message when I forget to use NZ() in a formula.\

But yes, you are correct.

The only reason I posted was to hint to Norbert2000 that perhaps "avoiding", rather than "hiding" the error might be a better way of dealing with this issue.

But as you have pointed out, there may be more at play here.
For that reason, this is still your "Q".


Enjoy your weekend!


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now