Access Hacks

Hack #13. Create an AutoNumber Field with a Custom Value

The AutoNumber field doesn't need to begin with a value of 1. You can override Access's default autonumbering scheme to better suit your requirements.

A great feature that Access brings to the table-creation process is the AutoNumber field. This field type places a value of 1 in the first record and automatically increases the value by 1 as records are added. It doesn't contain any significant or meaningful data. Its basic purpose is to become the key field and thereby provide uniqueness to the data records.

Just plop a field into the table design, and designate it as an AutoNumber field. Typically such a field has a name with ID or Num in it, such as CustomerID or RecordNum. Note that a table can have only one AutoNumber field.

All in all, AutoNumber is a great feature, but there is one gotcha: the value always starts at 1. Often, this isn't an issue because the field value really is unimportant. The fact that the values are unique is what matters more. But what if you need to use a self-incrementing number that starts at a different value? Can you do this? Of course!

Seeding AutoNumber with a Number of Your Choice

The AutoNumber field type doesn't have a property to specify the starting value. Figure 2-1 shows a table design. As you can see, the first field is an AutoNumber field, and its addressable properties fill the lower-left area of the table design window. Note that you have nowhere to input a default start value.