How to limit the digits of autonumber in office access ?

My question to you is why? Why use an arbitrary number as an ID? User IDs should be something easy for the user to remember. In my experience a userID is generally some combination of letters in the user's first and last name. Also, when logging in to an application, I generally use a combobox for the user to select themselves. Once validated the autonumber PK is what is stored in the system.

You might want to look at my blog on Login with VBA (see below for blog address).

There is also a blog article n Sequential numbering.

> Might lead to duplicate uid(s) if not managed carefully.

The only thing that is needed is to put an (additional) unique index on that field.

Hi Anand Gangwar,

Q1: how can I limit it to 5 digits?

There's no built-in way to directly limit the number of digits in an AutoNumber field in Access.

However, there is an alternative approach you can have a try without VBA code:

Use a Text Field with Validation:

• Change the data type of the uid field from "Autonumber" to "Short Text".
• In Design View, select the uid field.
• Go to the "Field Properties" section and find the "Input Mask" property.
• Set the Field Size to 5 if you don't have uids over 99999.
• Enter the following mask: 00000 (five zeros, enforcing a 5-digit format).

Q2:Should I use it as a primary key?

It's generally recommended to use the uid field as the primary key for your user table.

• Use as primary key:

1. Ensures each user has a unique identifier
2. Simplifies querying and filtering data based on uid(s).

• Don't Use as Primary Key:

1. Might lead to duplicate uid(s) if not managed carefully.
2. Finding specific users could require additional logic in queries.

Hope it helps you.

Regards,

Cristin

If you are going to use the value as a login id I would recommend that you use a short text data type.  You can then set the Field Size property to 5.  Make the column the table's primary key.

When inserting a new row into the Users table via a bound form you can compute the next number in sequence in the form's BeforeInsert event procedure and add leading zeros with:

   Me.UserID = Format(Val(Nz(DMax("UserID", "Users"), 0)) + 1, "00000")

Users will then have to enter their user ID as five characters, including the leading zeros, at login.