How to Reset AutoNumber in MS Access after Deleting Records

How to Reset AutoNumber in MS Access

How to Reset AutoNumber in MS Access after Deleting Records? It's very simple if you know how to do it. In fact, there are two ways to reset AutoNumber in MS Access after deleting records. One is using the Table property of Microsoft Access Database and the other is using VBA code. Follow me and I will show you how to reset AutoNumber in the MS Access table after deleting records step by step!

How to reset AutoNumber in Access

in the customer table, the Customer_ID field is a primary key, with the data type set to AutoNumber. This means that the Customer_ID is automatically assigned to each record by MS Access, and it is incremented by one (1) for each record. So, if you delete a record, the next Customer_ID will be one higher than the Customer_ID that was deleted.

If you need to start the Customer_ID number from one (1) on your new database, you can use the following procedure to reset the number of the AutoNumber field to one (1). To do this, you need to use the same table structure as in my sample download file, but with one (1) in the AutoNumber field. So now, when you insert records into the table, they will get an auto-generated number 1-99. 

If you have any problems or questions about how to reset autonumber in MS Access 2010/2013/2016 tables using VBA code or anything else I’ve written here, please leave a comment below and I’ll try to help out!

How do I reset AutoNumber?

In Access, how do I set the AutoNumber value?

You can add a primary key to a table by right-clicking it in the Navigation Pane, then choosing Design View. Select the first available empty row in the table design grid if you do not see the Navigation Pane. Press F11 to display it. Select AutoNumber from the drop-down arrow in the Data Type field.

How To Reset AutoNumber When Delete Record

How To Reset AutoNumber When Delete Record

Primary keys are fields or sets of fields in a table that are unique. They can be used to refer to entire records since every record has a unique value for the primary key. This article explains how and why primary keys are used. Access automatically creates a primary key field when you create a table, or you can specify which fields you want to use as primary keys.

How do I fix AutoNumber in access?

Go to the table design view and remove the primary key, then delete the AutoNumber field. right click to insert a new record and assign data type autonumber for that field. Click the ok button and confirm your changes by clicking on Yes when prompted with Are you sure you want to save these changes? message box.