An alternative to AutoNumbers
If there is a natural key for the table, then that may be used. However, there might be the problem that although there is a unique value for each object, at the time of entry this value isn't known. Therefore, you must use an artificial key. Access supplies its own built-in unique field, in the shape of an Autonumber. However, there are various problems with using an autonumber:
Another option that you might want to consider is creating a single field table, and storing the unique value in there until it is needed, at which point it is read out, and a new value created instead in the table.
A question that is often asked is how to reset the autonumber back, so that it starts incrementing from 1 again. Well, firstly, if you are using the autonumber datatype properly, then the value of the field should have no meaning whatsoever. Anyway, to reset the autonumber, delete all of the data from the table, and then compact your database. Now the first record that you enter into this table will start off at 1.
Something else that seems to be popular is to try and create and primary key field that uses the date in conjunction with an autonumber, for example 010120010001 might be the first record that was created on 1 Jan 2001, whilst 050220010015 would be the fifteenth record created on 5 Feb 2001. This is a very bad idea - the idea of relational databases is to store one piece of information in each field. If you wanted to do something like this, you would need to modify the code above slightly, and add a date field to the table:
You can then create a primary key for the table that is based on both the DateField and UniqueFieldName by selecting them both in the design view of the table by using the Control key and then clicking on the Primary Key symbol. You will then have a number that increments each day, and resets itself the next day.
Copyright & Disclaimer
Last modified at 06/06/2006 14:01:39