Key Value Submitted Already Exists
Troubleshooting Duplicate Key Values
In MS Access or Calibration Control (when using MS Access) sometimes attempting to create a new record results in an 'Insert Unsuccessful' error message because the key value already exists in the database.
There are three possible causes for this error. In order of likelihood, the possibilities are:
Possibility 1: Record Exists and Is Hidden
This possibility applies to Equipment records only. There might be a record with the same Equipment ID that has already been created in the database, but is 'hidden' from the main grid view. First, make sure all of the Equipment records are being displayed by right-clicking in the Equipment grid for the context menu and ensuring the 'All Equipment - Show Hidden' option is selected. Then use the filter row at the top of the grid to search for the record and change its Serial Number.
Possibility 2: Record Exists and Is Filtered Out
It is possible that a filter exists and is hiding a record with the same key value that you are trying to use. Enable the filter row by right-clicking in the grid for the context menu and checking the Filter Row option. Then click on the filter icon to the far left of the filter row which clears ALL of the enabled filters.
Possibility 3: MS Access Auto Number Error
The third possibility relates to the AutoNumber field in an MS Access table losing count of which number is next. For example, the AutoNumber field value for each row in a table with 100 records (none ever deleted) will be 1 through 100. So when record 101 is added the AutoNumber field should add the number 101.
The problem occurs when the AutoNumber field chooses a number that already exists (e.g., 95) thereby prompting the 'Insert Unsuccessful' error. AutoNumber fields always have a 'No Duplicate' setting. Additionally, this problem only seems to occur when the AutoNumber field is not the Primary Key, as demonstrated in the following image:
Although Calibration Control users have only reported this problem when creating new Equipment records (one table), there are five tables in 7.x versions and earlier of Calibration Control that are susceptible. These tables are:
- tblEquipmentMaster (Equipment Records)
- tblDepartmentCodes (Departments)
- tblJobEquipment (Jobs Related to Equipment)
- tblLocationCodes (Locations)
- tblStatusCodes (Status Codes)
To solve this problem, contact Ape Software and we will fix it for you at no charge. Otherwise, close Calibration Control and use MS Access to open the suspect table in Design Mode and move the Primary Key to the AutoNumber table.
If you cannot move the primary key, you may need to delete associated relationships first. Do this by closing the table in question and opening the Relationships tool in the MS Access Database Tools menu (2007 and higher). After the relationships are displayed, ensure all relationships are visible by clicking the All Relationships button in the menu. Delete any relationships that link to the AutoNumber field of the table in question and then retry the previous step.
For additional information on this MS problem, take a look at Garry Robinson's post for The AutoNumber "goes crazy" fix.
