[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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

Posted on 2009-07-13
Medium Priority
Last Modified: 2013-11-28
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?

Question by:Robert
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2

Accepted Solution

Milewskp earned 2000 total points
ID: 24841413
Have you tried using the form's OnError event property?
LVL 75
ID: 24841422
Which message is popping up ?

LVL 75
ID: 24841429
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.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58
ID: 24841592
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


Author Comment

ID: 24842384
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.


Author Comment

ID: 24842455
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24847877
<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.



Author Comment

ID: 24850030
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.

Author Closing Comment

ID: 31602902
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?


Expert Comment

ID: 24852237
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).

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question