Power Tip: Create an AutoNumber field that starts with a number greater than 1
Today’s guest blogger is Luke Chung, President and Founder of FMS, Inc. Luke has written and presented a wide range of topics related to Access over the years. In addition to their many Access related products, FMS offers a wealth of great Access papers, tips, and video on their site.
In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).
For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.
To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value). For instance, assume that you want to start with the Seed value 1000.
First, run this query:
INSERT INTO tblName ( ID ) SELECT 999 AS Expr1
Where tblName is the table name, and ID is the name of the AutoNumber field.
Then, run a second query to delete the row that you inserted:
DELETE FROM tblName WHERE ID=999;
(Alternatively, you can simply open the table and manually delete the row that you inserted.)
Now, the next record added to the table is assigned the value 1000.
This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. (Of course, the ID field for existing rows will not be changed, but subsequent rows will start with 1000.)