Primary key violation error - how can I trap it?

Posted on 2003-03-20
Medium Priority
Last Modified: 2010-05-18
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?
Question by:strDummy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

DrTech earned 200 total points
ID: 8173273
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.


Author Comment

ID: 8173809
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question