MS Access: Replace system error message with a custom message in Access 2003/XP/2000/97

Question:  In Access 2003/XP/2000/97, I have a form that is linked to an Oracle table. When I enter duplicate data, I receive an error message "ODBC - failed... Unique Constraint violated...".

Is it possible to define a custom message to replace the system error message whenever the primary key is violated? (ie: duplicate record)

Answer:  Yes, you can override the system error messages with your own messages. In your case, you want to place code on the Form's On Error event.

For example, you could place the following code on the Form's On Error event to trap the primary key violation:

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

    If DataErr = 3146 Then
        MsgBox ("You have violated the primary key.")
        Response = 0
    End If

End Sub

Now, whenever a user tries to enter a record that violates the primary key, the following error message will appear: