Key Value Submitted Already Exists

Cannot create a new record because it will create a duplicate key value

In MS Access or Calibration Control (when using MS Access), sometimes attempting to create a new record results in the following message stating that the, "Record cannot be saved because a key value already exists."

key value submitted already exists

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.  Another record with the Equipment ID you are attempting to use in your new record already exists and is hidden.  To ensure you are displaying all Equipment records, right-click on the Equipment grid and ensure the 'Show Hidden Records' option is selected.  Then try to find the Equipment ID you are trying to add.

show hidden records

Possibility 2:  Record Exists and is Filtered Out

All data grids in Calibration Control, including Equipment, have a filter row at the top that looks something like a blank row with some additional buttons.  It is possible that a filter exists and is hiding a record with the same key value that you are trying to use.  Since it is possible to accidently hit the space bar, thereby creating an invisible filter, it is a good habit to always use the "clear all filters" button at the far left of the data grid, circled in the following image.

clear all columns

Possibility 3:  MS Access Auto Number Error

The third possibility relates to the Autonumber field in an MS Access table loosing 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 the 101 record is added, the Autonumber field should add the number 101.

The bug occurs when the Autonumber field chooses a number that already exists (e.g., 95) thereby throwing the "key value already exists" error.  Autonumber fields always have a "No Duplicate" setting.  Additionally, this bug only seems to occur when the Autonumber field is not the Primary Key, as demonstrated in the following image.

autonumber not key field

Although Calibration Control users have only ever reported this bug when creating new Equipment records (one table), there are five tables in 7.x versions and earlier of Calibration Control that are susceptible to this bug.  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.

autonumber is key field

If you cannot move the primary key, you may need to delete associated relationships first.  Do this 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 all relationships that link to the Autonumber field of the table in question and then retry the previous step.

delete relationships

Again, if you need help with this problem, please contact Ape Software.

For additional reading on this MS Bug, the best source I found so far is Garry Robinson's post for "The AutoNumber 'goes crazy' fix".  Give it a read.

Last updated: 30 Oct 2013