The AutoNumber data type automatically generates a unique number for each record that’s added to a table, and is commonly used for primary key fields. Seldom, if ever, should the value of the AutoNumber field be shown to your database users. If the value of the AutoNumber field is relevant to your Access forms and reports, then it may be that you shouldn’t be using the AutoNumber type, since there will frequently be gaps in the numbers. Nevertheless, a recurring question from our students is: “How do you change the starting value of an AutoNumber field?” Try the following:
- Create a temporary table with just one field: a Number field. Set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
- Once you’ve created the temporary table, switch to Datasheet view and enter a value in the Number field that is one (1) less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
- Create a new append query. Add the temporary table to the query, and set the table whose AutoNumber field you want to change as table to append to. Run the query.
- Delete the temporary table, and delete the record added by the append query.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value one (1) greater than the value you entered in the temporary table.
Subscribe to Monthly Training Updates
Receive monthly productivity and training insights, software tips, and notices of upcoming classes!