Remove Access Validation Rule Error (1 Viewer)

hardhitter06 · Mar 19, 2010

Hi All (Access 2003),

I created a simple input form (frmVendorInput) with the fields 'VendorName' and 'VendorFedID'.

I set up custom code to alert the user when they have entered a duplicate Fed ID:

Code:

Private Sub VendorFedID_BeforeUpdate(Cancel As Integer)
 Dim Answer As Variant
 Answer = DLookup("[VendorFedID]", "tblInputNewVendor", "[VendorFedID] = '" & Me.VendorFedID & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Federal ID Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.VendorFedID.Undo
 Else:
 End If
End Sub

After this alert, Access displays its own validation message that I'd like to remove:

"The value in the field or record violates the validation rule for the record or field. For example you may have changed a validation rule without verfying whether the existing data matches the new validation rule."

Can someone let me know how I would remove this message. I will attach my DB and test with Fed ID "123456789" since that is already in the system.

Thank you,

Josh

llkhoutx · Mar 20, 2010

Set SetWarnings to False and trap the Access error with your message.

SOS · Mar 20, 2010

llkhoutx said:
Set SetWarnings to False and trap the Access error with your message.

No, don't do that.

Just move the code from the control's before update event to the FORM'S before update event and then it works.

Also you don't need to include the

Else:

part.

Pyro · Mar 20, 2010

Trapping the error in the forms' before update event works. But if you add more controls to the form, then that error won't be picked up until the entire form is updated, so if there are other controls that require data after you enter the VendorFedID, then you will go through that process first and then the error will be generated.

As an alternative, you could use the following:

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
    Select Case DataErr
        Case 2116
            Select Case Screen.ActiveControl.Name
                Case "VendorFedID"
                    MsgBox "Duplicate Federal ID Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
            End Select
            Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
    End Select
 
End Sub

SOS · Mar 20, 2010

Pyro said:
Trapping the error in the forms' before update event works. But if you add more controls to the form, then that error won't be picked up until the entire form is updated, so if there are other controls that require data after you enter the VendorFedID, then you will go through that process first and then the error will be generated.

As an alternative, you could use the following:

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer) Select Case DataErr Case 2116 Select Case Screen.ActiveControl.Name Case "VendorFedID" MsgBox "Duplicate Federal ID Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" End Select Response = acDataErrContinue Case Else Response = acDataErrDisplay End Select End Sub

Well, I don't know if you actually tried that (because I did) and

Error 3022 is the error number you want for a duplicate record, not 2116.

And second, it isn't the federal ID number that is a dup it is the NAME.

Pyro · Mar 21, 2010

SOS said:
Well, I don't know if you actually tried that (because I did) and

Error 3022 is the error number you want for a duplicate record, not 2116.

And second, it isn't the federal ID number that is a dup it is the NAME.

Firstly, yes i did test it.

Secondly, you are correct in that error 3022 is thrown at you if you enter a duplicate Vendor Name. However the OP was referring to error 2116:

Code:

"The value in the field or record violates the validation rule for the record or field. For example you may have changed a validation rule without verfying whether the existing data matches the new validation rule."

Error 2116 is what I am getting if a duplicate Vendor Fed ID is entered.

Which ever the error, my personal preference is for form level error trapping where feasible - i find it simpler to manage.

missinglinq · Mar 21, 2010

To suppress Access' standard error message, and display your own message, immediately after leaving the field, you can use the Form_Error event. This example is for Duplicate Value, Error 3022, but it can be modified for any error.

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim Message As String

If DataErr = 3022 Then 'Duplicate value entered
  Message = "You Have Entered a Duplicate Value For This Field:  " &   Me.ActiveControl.Name
  Response = MsgBox(Message, vbExclamation, "Duplicate Value Entered")
  Response = acDataErrContinue
End If
End Sub

Remove Access Validation Rule Error (1 Viewer)

hardhitter06

Registered User.

Attachments

llkhoutx

Registered User.

SOS

Registered Lunatic

Pyro

Too busy to comment

SOS

Registered Lunatic

Pyro

Too busy to comment

missinglinq

AWF VIP

Similar threads

Users who are viewing this thread