Resources

Online Shop

Info

Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available

Auto Numbering In 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.

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

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.

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

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.

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.

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.

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: