totn Access

MS Access 2003: Replace system error message with a custom message

This MSAccess tutorial explains how to replace the system error message with a custom message in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft 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.

Microsoft Access

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:

Microsoft Access

Share on: