Microsoft® Access Table
Duplicate Values Error Message

How to fix a problem of duplicate value errors when attempting to input a new record into an Access database table that has an AutoNumber data type applied to its primary key field.

Last updated on 2024-04-23.

The Problem

“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 are fine. Your intention is that invoices are numbered sequentially as they are generated. The InvoiceNo field with its AutoNumber data type achieves this for you.

Your InvoiceDetails table contains line-by-line information about the items covered by each invoice.

On checking the invoice records, I note that the last few invoices in the sequence, those numbered 689, 690, 691 and 692, received unusual attention. I believe that this is what has caused your problem.

At some point invoices 690 and 691 were deleted—I understand by someone who had made a mistake and wanted to start them afresh. Deletion left a gap in invoice numbers bewteen invoices 689 and 692.

In order to fill the gaps so that the numbering sequence would remain complete, your database administrator used an Append Query to re-introduce invoices 690 and 691.

I reckon that the appending of the numbers 690 and 691 to the InvoiceNo field left Access believing 692 to be the next number for a new invoice. As it tried to so, it found its attempt at a new 692 was a duplicate of the 692 still in the table.

A property of fields of AutoNumber data type is that they are excluded from accepting dupicate entries. Hence the duplicate value error message in this case.

Solution

These are the steps I suggest you take to restore the invoice numbering:

1. Take a back-up copy of your data
2. Take a manual record of the information for invoices and their details later than invoice 689
3. Remove the link between the Invoices and InvoiceDetails tables that is made between the respective InvoiceNo fields
4. Delete records for invoices beyond 689 in the Invoices table
5. Delete records for invoices beyond 689 in the InvoiceDetails table
6. Compact and Repair the database
7. Restore the links bewteen the Invoices and InvoiceDetails tables
8. Create a new invoice. Access should number it 690
9. Input into both tables the details for your original invoice number 690
10. Repeat steps #8 and #9 for your original invoices 691 and 692.

You should now have restored your invoicing to working order.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis 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.