my autonumber skipped a number, how do I fix it?

[Rikersplace]

Used auto number and during my entries, it skipped 212. And can't get it to
go back so that the entries are in numerical order if I delete the last entry
to try andfix the glitch.

 

[John W. Vinson]

Used auto number and during my entries, it skipped 212. And can't get it to
go back so that the entries are in numerical order if I delete the last entry
to try andfix the glitch.

It's not an error, and it's not a glitch.

Autonumbers have one purpose, and one purpose ONLY: to provide a meaningless
unique key. They are not guaranteed to be sequential, they will always have
gaps; you will get a gap if you delete a record, or even if you hit <ESC>
while entering a new record. That record's autonumber gets used up and will
never be offered again.

You can - if you insist! - run an Append query appending a record with a Long
Integer value 212 into the table. But it will just happen again.

If you want the field to be user meaningful, your only solution is to change
it from an Autonumber to a Long Integer and maintain it yourself (manually or
programmatically). When you're planning to do so consider: what will you do
when you have 2188 records in the table, and realise that record 3 was a test
record which is not "real data" and must be deleted? Will you renumber records
4 through 2188; all the records in all the related tables; all the printed
reports with those records; all the Post-It notes and human memories involving
those records...!?

 

[Rikersplace]

Thanks for the answer and sarcasm. I "needed" to remember I was asking
linear thinking members a simple question. The glitch was human error, I
know that. But, again, thanks for the seething sarcasm and reality check.

 

[John Spencer]

Gee, I guess I missed the sarcasm. John was trying to make a point and
make sure you understood the point. Perhaps he got too detailed in his
response, but it is difficult to judge anyone's level of knowledge from
one posted question.

That being the case we often guess wrong and give too curt an answer or
too detailed of an answer. Most of us tend to err on the side of too
much detail as that will usually leave the novice user less confused and
the more expert user can just ignore the extra details.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================