Validation Rule Violation Message-Disabling

[jalge]

I have a subform with a date field on it. For that field, I have set a validation rule. When you enter a date that violates the rule, my custom text comes up just fine.

Then I get a message that says "The value in the field or record violates the validation rule for the record of field."

I searched the forum and found some previous comments on how to trap this error, (presumably Err #3022). I added standard error trapping code on the after update property of the date field, but I can't seem to get the code to work. I still get the info box.

Here is the code I tried:

Private Sub Date_AfterUpdate()

On Error GoTo Err_Date_AfterUpdate

Exit_Date_AfterUpdate:
Exit Sub

Err_Date_AfterUpdate:
If Err = 3022 Then
Exit Sub
Else
MsgBox Err.Description
Resume Exit_Date_AfterUpdate
End If
End Sub


Any suggestions?

 

[Mile-O]

If Err.Number = 3022

 

[jalge]

I tried your suggestion. I'm still getting the message.

Maybe the error number is incorrect. Do you know where I can get a list of the message codes?

 

[Waltang]

change your line that says

MsgBox Err.Description

to be

MsgBox err.number & " - " & Err.Description

Then see what number it is returning.

 

[jalge]

Waltang,

I entered the code, but it is not returning anything. The program is responding the same as before- with my custom message followed by the Access built-in message.

Is there another way to force identification of this error code?

 

[Mile-O]

Use the Before Update of the control in your subform instead of the validation rule and it will work.

 

[jalge]

I tried all these things, and cannot get it to work. I did discover the error number was wrong. 3022 is for key violations. I cannot get the computer to return a dialog box that gives me the correct error message for the validation rule violation.

Is there a way to do away with the validation rule property, and use code to perform the validation check?

The validation rule checks to ensure the date entered is less than or equal to today, and within one year of today. The code I use in the validation rule is:

<=Now() and >=Now()-365

Suggestions?

 

[Mile-O]

<=Now() and >=Now()-365

Suggestions?

Suggestion, to begin with, is to use a better validation rule than that...


Between DateAdd("yyyy", -1, Date()) And Date()

 

[jalge]

The Solution

Give an inch, take an mile? I figured it out, so here is my inch.

The error code for a Validation Rule Violation is #2116. I added the following code to the OnError property of my subform:


Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 2116 Then
Response = acDataErrContinue
End If

End Sub

Pretty rudimentary, but it works. I no longer get the Validation Rule Violation nor other error messages.

 

[Pat Hartman]

Your validation rule should use the Date() function rather than Now(). Now() returns date and time whereas Date() returns only date.

 

[jalge]

Thank you for the advice. I changed the validation rule to:

Between Date()-364 and Date()

The one suggested earlier does not seem to weed out all the problems. The main purpose of the rule is to keep users from entering the wrong year or garbage dates, which has been a problem with the old system.

 

[Pat Hartman]

If a field is defined as date/time in the table, there is NO way to get Access to accept invalid data. Your validation rule is applying a business rule which is that dates must be not be in the future and not more than 365 days in the past.

<=Date() and >= Date()-365

 

[jalge]

I used 364 instead of 365 days to ensure they don't enter the wrong year. I tested it, and on certain months, you can enter today's date (month and day), but have the year as last year, and it would accept it.

By "garbage" dates, I mean the year was way off. These are time logs for attorneys, and all the dates should be within the current year. We had some entries that were for years far in the future (like 2010) or the past (like 1969). It was merely bad data entry.

Thanks for your help!