Primary key violation error - how can I trap it?

I'm having problems trapping a primary key violation error.

I have a join table to implement a many-to-many relationship between two tables, Department and ResearchGroup, i.e.

PK Department_ID (number)
PK ResearchGroup_ID (number)

To enforce the rule: "No ResearchGroup may be joined to the same Department twice" I've used a compound primary key.

In my Department form I have a subForm that is a datasheet view of the Join table which allows me to update the list of ResearchGroups that are associated with the Department.  The datasheet displays one column, the name of the ResearchGroup as a combo box so that it can be changed to another valid ResearchGroup.  The problem is that if I select a duplicate ResearchGroup in the subform then a I get an error message that my users wont understand.  So I wish to trap the error and present something a bit more meaningful. However I'm having some difficulty trapping the error.  

The steps to replicate the error are:
1. I update a row so that it is a duplicate of another
2. I then try and move the focus to another row in the datasheet
3. Error is raised

The Error text that I see is:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I was able to trap the error with the Form_Error event but I was unable to prevent the standard error message being raised (with an err.clear call).  I have tried trapping this error in events raised by the ComboBox and the sub Form (using on error goto..), but without success.  Any ideas folks?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In the Form_Error event, add this line after handling the error:

Response = acDataErrContinue

Look in the online help under Error Event, and click Event Procedures for further information.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
strDummyAuthor Commented:
Thankyou DrTeck, that is exactly what I was missing.

Just to be thorough, here is the Form_Error subroutine I used:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo ErrorHandler
  Select Case DataErr
    Case 3022 ' primary key violation
      MsgBox "You cannot select that Research Group as it already exists in the list, " & vbCrLf & "please select another one.", vbCritical, "Duplicate Research Group!"
      Response = acDataErrContinue ' surpress standard message
      If IsNull(ResearchGroup_ID.OldValue) Then
        ' insert failed - take out the offending entry
        ' udpate failed - set back to old value
        ResearchGroup_ID.Value = ResearchGroup_ID.OldValue
      End If
    Case Else

  End Select
  Exit Sub
  ShowError ("Form_Error(DataErr = " & DataErr & ", Response = " & Response & "):Error")
End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.