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.
- Backup your database
- Open the access database
- Open the broken table
- Change the field from “AutoNumber” to “Number”
- Switch to View mode and add the missing record, here it was “6914”
- 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
- Copy the Table
- Paste only the structure of the table
- Change the field to “AutoNumber” and save it
- Create a query with query wizard as shown below
- Switch to design mode an append to the structure only table
- Close and open the structure table
- Rename delete the original table and rename the structure table to the original name
- Create a new record, now you should get a new ID without missing records