Weird autonumber issue

[quepi]

I've developed a simple form that uses an autonumber as the key field. For reasons I don't understand, when we finished entering the 9th record and went to enter a new record, the autonumber skipped "10" and went straight to "11" so that now the number of records doesn't jive with the autonumber. I suppose it's not a huge issue, but it is annoying and I'd like to fix it. I'm guessing there's probably an easy solution that someone with much more Access experience than I have can provide. For what it's worth, I'm using Access 97.

Any clues? Thanks in advance for the help!
Kris

 

[JoeMiller]

Autonumber doesn't skip numbers. It issues a number only once and never again. So what probably happened is someone started to enter a record, it assigned 10, they then hit ESC and cleared the record (perhaps a typo?) and 10 disappeared. Now the next record entered would be 11 as in your case. Try it and you'll see what I mean.

HTH Joe Miller
joe.miller@flotech.net

 

[quepi]

Thanks Joe,

That makes sense, but is there any way to get "10" back? or is it doomed to eternal data limbo? Even if we escaped out of it, shouldn't we still see it in the datasheet view as a "deleted" record?

Thanks again for your advice. - Kris

 

[JoeMiller]

The only way to get numbers back is to run compact on your database. But! Compact will only return numbers that are 1 digit higher than highest value in your table. What this means is that if you had a record number 9, ESC out of 10, then saved record 11 you can't get 10 back. But if you have record number 9, ESC out of #10 through 1000, when you compact you'll start over again with number 10.

As for #Deleted, no, you won't see that in native Access tables. You'll see it in some linked tables and such but Access hides the deleted records from you in it's own tables.

HTH Joe Miller
joe.miller@flotech.net