Alison Balter's Mastering Access 2002 Enterprise Development

Creating Custom Counters

Access offers an AutoNumber field type that can be set to automatically generate sequential or random values. Although the AutoNumber field type is adequate for most situations, you might want to home-grow your own AutoNumber fields for any of the following reasons:

• You don't like the AutoNumber field discarding values from canceled records.

• The primary key value needs to be some algorithm of the other fields in the table (the first few characters from a couple of fields, for example).

• The primary key value needs to contain an alphanumeric string.

To generate your own automatically numbered sequential value, you should probably build a table that holds the next available value for your custom AutoNumber field. You must ...