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.
LVL 1
Norbert2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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)President / OwnerCommented:
<<#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)President / OwnerCommented:

  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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.