Increment AutoNumber

Question

Hi

I have a question about AutoNumber which is Increment. This number increase by 1 every time new record create. If I have thousand records the next record will has 1001 number. If I delete all records and start from beginning the new record start at number 1001. Is there any way to reset the AutoNumber to start from number 1.

Best Regards

Answer 1

You mean that it is better to do not use the autonumber and depend on a number which can be reused but not duplicate. 

That's not exactly what I mean.  It depends on what your autonumber field would be used for.  Autonumbers are great for generating unique identifiers that will be used internally to link related records.  In those cases, it doesn't matter what the number value is, and no one cares if the numbers are consecutive or have gaps in the sequence, so long as you have a unique key for each record.

Where autonumbers can cause problems are cases where they will be exposed to users, and the users will have expectations about them.  For example, auditors tend to get upset with gaps in a sequence of invoice numbers, or even worse, check or deposit numbers.  They want to know what happened to the "missing" transactions.

Some people say that you should never expose autonumbers to users under any  circumstances.  That doesn't mean you don't use them; it just means you use them only internally, and don't show them to the users.  If the users need an ID number, you generate one over whose value you have more control, and for which you can ensure that there are no gaps in the number sequence.  For that, you can use techniques like the one Ken Sheridan demonstrates in the database he linked to.

I will say that I don't completely hold with the rule to *never* expose an autonumber to the  user.  I haven't had a problem using an autonumber for an external ID in an application where I wouldn't expect any normal user to associate any sequence with the ID values -- identifiers for abstract entities that correspond to nothing concrete or "countable" in the real world.  But for invoice numbers and things like that, autonumbers are right out.

Answer 2

After deleting all the records, compact the database while the table is empty.  Then the next record you enter will get autonumber 1.

Note that it's not a good idea to rely on the specific values of autonumbers.  You don't control them, and in normal operations gaps in the number sequence will develop.

Answer 3

Thanks for your answer. You mean that it is better to do not use the autonumber and depend on a number which can be reused but not duplicate. 

Best Regards

Answer 4

As Dirk has pointed out, relying on an autonumber to give you an unbroken sequence of integer values is not advisable.  An autonumber is really designed to guarantee distinct values, not necessarily sequential values.  For the latter it is better to compute the number when a row is inserted into the table.  You'll find an example in CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option for sequential numbering computes the next number when a record is inserted via a bound form.  It handles the key violation error which will result in a multi-user environment if two or more users are inserting a record simultaneously, and allows the next number to be used to be 'seeded'.