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!

2.2.1. 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.

Figure 2-1. AutoNumber, an incrementing field type

To be clear, the table does contain a New Values property, but all it tells you is whether new values are incremented or are random. It tells you nothing about starting the increment at a value of your choice. So, the first record will have a value of 1 in the field, the second record will have a value of 2 in the field, and so on.

To override the default starting value of 1, you can use an Append query to insert a different starting value. After you have designed the table and are ready to use it, you must get the initial value in place. Figure 2-2 shows an Append query that specifically places a value in the AutoNumber field. That is, one record gets added to the table, with the AutoNumber field receiving the designated value.

Figure 2-2. Using a query to set the beginning AutoNumber value

Note that you enter this query in the SQL view. That's because it's not obvious how to enter this query in the design view (the query grid), in which Append queries are typically used to append one table to another. This operation works by appending a value to a field in a table with no other table involved (in a pinch, you can design another table just to hold the value, but you don't have to do so).

Figure 2-3 shows the results of running the Append query. The Employees table was empty but now it contains its first record, and the AutoNumber value for that record is 100.

Figure 2-3. The first record with the designated starting AutoNumber value

Note that no other fields have been populated yet. If any other fields must have a value, you must populate them with appropriate values in the query. For example, you can modify the query to populate additional fields, such as Employee and Title, like this:

	INSERT INTO Employees (EmployeeID, Employee, Title)
	VALUES (100, 'John Smith', 'Supervisor');

Figure 2-4 shows the result of applying this new query. On the surface, this seems to take care of two birds with one stonestarting the AutoNumber with a value of your choice, yet without using a dummy record (as in Figure 2-3) to do so. However, this approach can be problematic. It's a little odd to populate the first record in this manner and then to populate all subsequent records via forms, processing, or other methods. The point is that it probably isn't practical to populate the first record in any method that differs from how other records are inserted.

Figure 2-4. Using a query to fill the AutoNumber field, along with other fields

But how can you get the first record to have the desired starting AutoNumber value without having a dummy record as the first record in your table? The twist is to still populate the first record using a query, but to populate the AutoNumber field with a value of one less than the real starting value. You can then delete this record, and the AutoNumber will increment as sub-sequent records are added. This means the first real data record, entered in whatever way your system handles it, will have the first value of choice. The AutoNumber will just increment from there, as expected.

In the example shown in this hack, the query needs to populate the table with a single record, in which a value of 99 is given to the AutoNumber. That record is then deleted (manually or otherwise; it doesn't matter how). When the first real data record is added, it will have an AutoNumber value of 100.

2.2.2. Hacking the Hack

You can reset the value of the AutoNumber field whenever you want. This doesn't change the existing records. It lets new records be numbered starting from a new initial value. You just run the Append query, as shown in Figure 2-2 (adjusted as discussed to handle any other required fields), but reset the value to one that is higher than the highest value already in the table. For example, if the last record entered in the table has a value of 220, reset the count to something higher. Obviously, you would skip the next incremental number; otherwise, there would be no reason to reestablish where the increment begins.

This offers an interesting option for managing your data. What if a series of records in a table has some unobvious but related attribute? For example, you can reset the AutoNumber field to a new starting value at the start of each year. Then, each year's data will be easy to distinguish. For example, all records in the year 2005 might lie within the 5,0005,999 range, all records for 2006 within the 6,0006,999 range, and so on.