A fix for an AutoNumber field that has given up the ghost

MS Access Autonumber Field Duplicate-value Error Message


Question

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.

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.

In so doing, 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.


Solution

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
  • Delete records for invoices beyond 689 in all tables linked via the InvoiceNo field
  • Compact and Repair the database.

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.


Disclaimer

DMW Consultancy Limited 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.