• 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.

Thanks,
Norb.
0
Norbert2000
Asked:
Norbert2000
  • 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:

=AvoidError([<ReferenceHere>])

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

JimD.



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

Trap:
    AvoidError = 0
    Resume Next

End Function
End Function
0
 
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:
=AvoidError(Forms![<MyFormName>]![<MyControlName>]

Original control control source:
=SUM([<MyFieldName>])

  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.

JimD
0
 
Jeffrey CoachmanMIS LiasonCommented:
Norbert2000,

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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21636779.html?sfQueryTermInfo=1+error
(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
:)

JeffCoachman

BTW her is a search here of "#Error"
http://search.experts-exchange.com/advancedSearch.jsp?ssfTAIDs=39&ssfTerms0=%23Error&sfNeglectedOnTop=false&ssfTermType0=1&ssfTermCount=1&advancedSearchSubmit=true&ssfConjoiner0=null&ssfTermInclude0=true&ssfTermScope0=5&sfWithinTA=39
0
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.

 
sonchoyCommented:
what is error? Can you write error#
0
 
Jeffrey CoachmanMIS LiasonCommented:
sonchoy,

The error is:  #Error

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

JeffCoachman
0
 
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.

JimD.
0
 
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.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
JDettman,

<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.\
:-O

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!
:-)

JeffCoachman
0

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