Cannot create a new record because it will create a duplicate key value
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."
There are three possible causes for this error. In order of likelihood, the
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.
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
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.
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
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.
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.
Again, if you need help with this problem, please contact
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. It was Gary who personally set me straight on the problem
so THANK YOU Gary!!
Last updated: 30 Oct 2013
Compatibility: Calibration Control versions 5.x