Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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?
1 Solution
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.

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now