Generic Error Message in Access Form - How to Block It?

I have a form with validation rules defined in several fields.  I've also added Validation Text, which pops up as I'd hoped.  However, the first time the error happens in a particular field, the Validation Text popup is followed with a more generic - and confusing for end-users - popup message:

The value violates the validation rule for the field or record

How can I prevent this message?

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.

Have you tried using the form's OnError event property?

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Which message is popping up ?

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In addition, instead in doing validation at the table level with rules, etc ... consider using the Form Error event - which is where most of these sorts of errors can be trapped.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
To extend the OnError comment a bit, use acDataErrContinue to supress the built-in message.  You do something like the code attached, which is from the on-line help.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
	Const conDuplicateKey = 3022
	Dim strMsg As String
	If DataErr = conDuplicateKey Then
		Response = acDataErrContinue
		strMsg = "Each employee record must have a unique " _
			& "employee ID number. Please recheck your data."
		MsgBox strMsg
	End If
End Sub

Open in new window

RobertAuthor Commented:
Sometimes I scratch my head and wonder why or how something works under the level I see.

Ok, I added a "Do Nothing" macro on the form's OnError Property.  The extra popup no longer appears.

Attached are two screenshots - one with the field's Validation Text message popup - the 2nd with the generic validation rule popup.

Thanks for the code sample, although I didn't need to test it.

RobertAuthor Commented:
The Validation Rule seems to be operating properly - why does it generate this "run-time" error?   I'd like to understand Access a bit better!

Not sure why some form fields with basic validation rules don't get these popups, and yet this slightly more complicated form (validation rules are defined in part with data from an underlying form).  

The other forms I have also have Validation Text, yet don't get the 2nd popup.
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

1. <The Validation Rule seems to be operating properly >
As MX states, you may not want to use the validation Rule/Text properties at all.
Then this whole issue should go away.

2. <"run-time" error>
What "run-time" error?
I don't see the words "Run Time error" in any of your screenshots?

3. I would not be using Macros for this, you have much more control by using VBA.


RobertAuthor Commented:
Jeff, all,

>1. <The Validation Rule seems to be operating properly >
>As MX states, you may not want to use the validation Rule/Text properties at all.
>Then this whole issue should go away.

I have other forms where the Validation Rule/Text (VR/VT) work fine - they don't kick back the 2nd (generic message) when data entry/mod fails the Rule.   That's why I'm asking, why would a VR/VT kick back the 2nd message on some forms/fields, but not on others?

>2. <"run-time" error>
>What "run-time" error?
I> don't see the words "Run Time error" in any of your screenshots?

An error is being triggered.  As Milewskp suggested, trapping it by using the OnError event "solved" the problem.  I added a Macro, with one line that is conditioned never to execute, and the generic VR message no longer pops up.

>3. I would not be using Macros for this, you have much more control by using VBA.

I concur.   My experience with Macros is that they don't work as advertised.  I have stepped thru macros where they end - without a StopMacro action -  before all the actions have been executed or even evaluated.  I have stepped thru a macro where it says it is changing a property on a form, yet the modified property is not triggered until after the form is somehow refreshed (go to Design View, then back to Form View).  There are some actions that allow an Expression to be 'No' or 'False', and others that require the value 0 (zero).  Or issued a GoToRecord that fails because the macro error says that the form is not open - when it is.

It's enough to set the clock at 5 and start mixing margaritas.

just my $0.02 - I'll get off my soapybox now.
RobertAuthor Commented:
Thanks - this workaround hides the symptom.

I have other forms with validation rule & text on fields that don't kick back the generic validation rule message.  What causes this message to kick back on some form fields, and not others?

The are several levels of data validation (see attached table). Your validation text pops up for the rules you defined, and the 'confusing' messages pop up for the others (unless you trap with OnError).
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.