Solved

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

Posted on 2009-07-13
10
274 Views
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?

ub
0
Comment
Question by:Robert
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 1

Accepted Solution

by:
Milewskp earned 500 total points
Comment Utility
Have you tried using the form's OnError event property?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Which message is popping up ?

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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.

mx
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
JimD.

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

0
 

Author Comment

by:Robert
Comment Utility
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.


Field---Days-After---Validation-.JPG
General-Validation-Rule-Popup.JPG
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:Robert
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
FWIW,
<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.

JeffCoachman

0
 

Author Comment

by:Robert
Comment Utility
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.
0
 

Author Closing Comment

by:Robert
Comment Utility
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?


0
 
LVL 1

Expert Comment

by:Milewskp
Comment Utility
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).
VR.doc
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now