Microsoft Access – Add missing record for Auto-Number records

One of my customers use an access “application” where surprisingly one record was missing for an auto-number field. Therefore one invoice number was missing which can lead to many questions when the finance is asking where the number is.

Here are the steps how to add a the missing record.
Note: Its a little bit complicated for such a simple task but i found no other solution.

  1. Backup your database
  2. Open the access database
  3. Open the broken table
  4. Change the field from “AutoNumber” to “Number”
  5. Switch to View mode and add the missing record, here it was “6914”
  6. Check if you have now the right number of records according to the number of the last record, here it was the invoice number 6942
  7. Copy the Table

  8. Paste only the structure of the table

  9. Change the field to “AutoNumber” and save it

  10. Create a query with query wizard as shown below

  11. Switch to design mode an append to the structure only table

  12. Close and open the structure table
  13. Rename delete the original table and rename the structure table to the original name
  14. Create a new record, now you should get a new ID without missing records

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top