Primary key violation error - how can I trap it?
Posted on 2003-03-20
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?