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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
https://www.experts-exchange.com/questions/21636779/error.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
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:
https://www.experts-exchange.com/questions/21636779/error.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
what is error? Can you write error#
sonchoy,
The error is: #Error
#Error is what shows up in the textbox if Access cannot perform the calculation.
JeffCoachman
The error is: #Error
#Error is what shows up in the textbox if Access cannot perform the calculation.
JeffCoachman
<<#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.
It also displays #Error if a recordset is empty even though a calaculation may be valid.
JimD.
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.
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
<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
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![<MyForm
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