Trap Duplicate Index error

[Jim]

I have a form in Access 2003 that has a table as control source.

When the form writes to the table with a duplicate key I get a message
that the "changes requested were not successful because they would
create duplicate values in the index... etc etc"

Where can I trap this error?

 

[Jim]

I found this:

form_error event

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

If DataErr = 3022 Then
MsgBox "Error Description..."
Response = acDataErrContinue
End If
End Sub

 

[Klatuu]

I prefer a different approach.
Use the Before Update event of the control where you enter the value that
may be a duplicate.
Use a DLookup to determine if the value already exists in the table.
If it does, cancel the update and notify the user.

Something Like:

Private Sub txtSomeField_AfterUpdate(Cancel As Integer)

If Not IsNull(DLookup("[SomeField]", "[MyTable]", "[SomeField] = " &
Me.txtSomeField) Then
Cancel = True
MsgBox Me.txtSomeField & " Is Already in the Table"
End If
End Sub