I have a many to many relationship, and one of my tables (the junction table) has only 2 fields (BookID & AuthorID). These fields together are set as primary key, in order to avoid duplicate records. Eventually, if i will try to put a duplicate record, i will get Access error's message "The changes you requested to the table were not successful because they would create duplicate values in the index, primarykey or relationship ..."

What is the best approach in order to check whether a duplicate record is going to be created, and if yes, i will get a customised error message rather than the above mentioned default error message ?

Thanks

Not sure if this is the best method but it certainly works for me on a similar situation

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

Select Case DataErr
Case 3022
MsgBox "Your message", vbOKOnly
Response = acDataErrContinue
Me.Your Primary Key 1 = ""
Me.Your Primary Key 2 = ""
Case 2237, 3162, 3314
MsgBox "Your Message", vbOKOnly
Me.Undo
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

End Sub

thx for the suggestion. By the way why did you use those 4 specific error codes ?

This code was given to me awhile back when I was trying to do the same as you

it was probably from another application, so I guess you can remove or change the error codes to suit.

.. :)

thx for the info :)