MS Access Autonumber Field
Duplicate-Value Error Message
Last updated on 2018-07-30 by David Wallis.
“We have a table that has an AutoNumber primary key field. When we try to input a new record, Access gives a primary key duplicate value error. We have used this type of field before and never had this problem. Can you tell us where we are going wrong?”
Diagnosis of the Problem
The design of your “Invoices” table and your use of the AutoNumber data type for the primary key field is fine. Your table records invoices. The AutoNumber field is named “InvoiceNo”. Your intention is that invoices are numbered sequentially as they are generated.
Your Invoices table is linked to your InvoiceDetails table, which contains line-by-line information about the items covered by an invoice.
On checking the invoice records DMW note that the last few invoices in the sequence, those numbered 689, 690, 691 and 692, received unusual attention and we believe that this is what has caused your problem.
At some point invoices 690 and 691 were deleted, we understand by someone who had made a mistake and wanted to start them afresh. In order to fill the gaps so that the numbering sequence would remain complete, your database administrator used an Append Query to re-introduce 690 and 691.
We reckon that due to the appending of the numbers 690 and 691 to the AutoNumber field, Access was left with the belief that 692 would be the next number it should autonumber for a new record. As it tried to so it found the attempted new 692 was a duplicate of the 692 still in the table—hence the duplicate value error message.
These are the steps we suggest you take to restore the invoice numbering:
- Take a back-up copy of your data
- Take a manual record of the information for invoices later than invoice 689 in the invoice table and all other tables that link to it
- Remove the link between the Invoices and InvoiceDetails tables that is made between the respective InvoiceNo fields
- Delete records for invoices beyond 689 in the Invoices table linked
- Compact and Repair the database
- Restore the links bewteen the Invoices and InvoiceDetails tables
When you open the database and input a new invoice it should be numbered 690. Rather than trying any Update or Append Queries, you may feel more confident to input the information for invoices 690, 691 and 692 manually, taking care to match input to the information in printouts of these invoices you may have made before the problem arose.
Give DMW’s Tips Your Support
ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal.
Thanks, in anticipation.
DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.