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 the 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 we note that the last few invoices in the sequence, those numbered 1989, 1990, 1991 and 1992, received unusual attention and we believe that this is what has caused your problem.

At some point invoices 1990 and 1991 were deleted, we understand by someone who had made a mistake on them 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 1990 and 1991.

In so doing, we reckon that due to the appending of the numbers 1990 and 1991 to the AutoNumber field Access was left with the belief that 1992 would be the next number to pick for a new record. As it tried to so it found the attempted new 1992 was a duplicate of the 1992 still in the table — hence the duplicate value error message.

solution

These are the steps we 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 later than invoice 1989 in the invoice table and all other tables that link to it
3. Delete records for invoices beyond 1989 in all relevant tables
4. Compact and Repair the database.

When you open the database and input a new invoice it should be numbered 1990. Rather than trying any Update or Append Queries, you may feel more confident to input the information for invoices 1990, 1991 and 1992 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.