databasedev.co.uk - database solutions and downloads for microsoft access

What is it and when should it be used?

Auto numbering is a handy little feature that will search out the next consecutive or random number for you, and as the name suggests, it does it automatically. It then adds that number into the field where the data type is set to 'AutoNumber'.

When talking about Microsoft Access, auto numbering should only be used when you want to create a primary key, but don’t want it to have any real significance to it, other than the fact that it is unique.

Is there a time when auto numbering shouldn't it be used?

As a matter of fact, there is. You should never use auto numbering for numbers that mean something to you, the item itself or product that is being stored. This is because if you ever needed to change it, which you most likely will want to sometime in the future, you won’t be able to using this method.

Is there anything I should know about auto numbering that could go wrong?

The answer to that is ultimately 'yes'. Auto numbering your items can lead to spaces or 'gaps' being left inside your table. These spaces can never be recovered once they have been created. They are created because auto numbering only guarantees that the number hasn't been used before – they don’t guarantee that they are in sequence. Anytime you create a record, but it gets cancelled or deleted, you will end up with a space in your auto numbering field that cannot be deleted or changed.

How do you use it?

It's very easy to set a field to auto numbering. When you are in the design view for your Microsoft Access database, just choose a field name, and then set its data type to be 'AutoNumber'. From there you have a few choices to make.

1. You get to decide the length of the field using the 'Field Size' option in the 'General' window. This can be set to either 'Long Integer' or 'Replication ID'.
2. Next you will be able to choose how the new values are created using the 'New Values' area in the same window. This will allow for either incremented values or random ones to be generated as you go.
3. After that, you'll be able to choose the format you want the numbers to be in using the "Format" option. There are several choices in this drop-down menu, some being 'General Number', 'Currency' and 'Percent'.
4. The fourth option you get is choosing a caption for the field. If none is selected, then the field name will be used as a default.
5. Next, there is the option of 'Indexed'. Here you can choose between 'Yes' and 'No', with 'Yes' giving you the extra option of allowing or not allowing duplicate values.
6. Finally you can choose if you want any smart tags to be applied to the field using the 'Smart Tags' option.

What can you use instead of setting up an auto numbering field?

If you don't wish to use the auto numbering feature in Microsoft Access, you could always create a system of your own. This is not a good idea if you wish to be working with randomly generated numbers, because it would be very difficult to tell just which ones have already been used.

If you were looking for an alternative way of creating an incrementing number however, one of the ways people usually do this is to use the DMax domain aggregate function.

See: Using the DMAX function to Automatically Increment a Fields Value in a Microsoft Access database

Is there a way to change these numbers to plain text after creating them as auto numbers?

Yes, by pressing ALT-F11 you will switch to VBA mode and from there, you can type activedocument.Range.ListFormat.ConvertNumbersToText into the lower right window. That will switch all the auto numbers you had generated in that field to text ready to be used elsewhere if needed.

Readers of this article may also like to check out other articles on this subject:

• How can I reset the initial value of a counter (Microsoft Access AutoNumber) field?
• Using a Microsoft Access Append Query to Set the Initial Value of an AutoNumber Field
• Resetting a Microsoft Access 2003 AutoNumber