Autonumber Field Issue

[David]

I have a field in an existing table which is formatted to
an autonumber field. I am up to 16410 records and when I
try to generate the next record, instead of 16411, I am
getting a number like 1927871771. Subsequent records
increment from this new large number. I need to get the
number to sequence from 16410 for control reasons. How
can I do this?

 

[Simon]

Hi David,

Just so happens tht I have another AutoNumber probem.

However, are you sure that you haven't got the increment set to "Random"
instead of "1"?

Simon

 

[Roger Carlson]

An autonumber field should never be used to hold data with meaning. The
user should never care what the value is. The problem with autonumber is
that you do not have any control over its value and it is DESIGNED to be
that way. It is very easy to loose a number in the sequence. All you have
to do is start a new record and then cancel without saving. That number
will be gone forever.

As to HOW it happened, that's hard to say exactly, but a simple Append query
where you attempt to set the autonumber will do it. Suppose we had a table
"Authors" with an autonumber field AuID. If I run the following query:
INSERT INTO Authors ( AuID ) VALUES (99999999);
It will not append the record, but the autonumber will start at 100000000.

In Access 97, you could just delete the higher records and Compact and that
would reset the autonumber, but that does not work in Access 2000+.

If you must have a sequential number without gaps, you should code your own.

On my website is a small sample database called "AutonumberProblem.mdb"
which explains more and illustrates a solution.