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

Posted on 2009-07-13
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
  • 4
  • 2
  • 2
  • +2

Accepted Solution

Milewskp earned 500 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.

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 57
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Normalization of a table 19 75
unable to create table-based data macro in MS ACCESS 2013 11 25
Calculation in Access 5 26
Binding recordsets to a form 6 27
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 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