Accidentally deleted an autonumber# record, now missing a number

[HvitrEbrithil]

Like the title said, I accidentally deleted a record from my table, now the number is missing, and I can't find a way to get it back. I might be a perfectionist, but I really want the number list to be complete, and not missing any numbers. Any way to fix this?


Example of the problem:


Thanks in advance

[Bob Fitz]

You could try deleting any records >172. Then Compact and Repair. Then close the db and re-open it.
However, IMHO this is a pointless exercise as number used in the Auto-Number field has no meaning. It is just a unique identifier for each record.
Numbers will go missing anyway. If you start to enter a new record and then press the Esc key to abort that entry, the number that it would have used will be lost.

[HvitrEbrithil]

I see.. Yeah I know it's pointless in a way, but I'm also using this as a referance to the episode-number in my list, and it will get messed up in the future for all episodes after season 8. I could add a new column that numbers them one extra time, but it seems unnecessary. Thanks for the help, it helped me understand the autnumber function better!

[HvitrEbrithil]

Tried the method you suggested with compact and repair, worked like a charm! Thanks a lot Now my OCD can rest carelessly

[Bob Fitz]

I see.. Yeah I know it's pointless in a way, but I'm also using this as a referance to the episode-number in my list, and it will get messed up in the future for all episodes after season 8. I could add a new column that numbers them one extra time, but it seems unnecessary. Thanks for the help, it helped me understand the autnumber function better!

I would strongly advise against using the Auto-Number for anything other than as a unique identifier, as I said earlier, for the reason I also stated then.

[Beetle]

Now my OCD can rest carelessly

Until the next time you delete a record or Autonumber randomly throws in an out of sequence number. Autonumber is not guaranteed to be sequential, or even positive for that matter, only unique.

[ssanfu]

Just as an FYI, see:

http://access.mvps.org/access/general/gen0025.htm
http://www.utteraccess.com/wiki/index.php/Autonumbers

[HvitrEbrithil]

@Bob Fitz

I see now that it's not a very clever way to go... Hmm, I will consider adding another field with the incremental number, but I'm not sure yet. I find it lacking in aesthetics to have to nearly similar fields.

@Beetle

Point taken. I guess I'll live and learn! At least for future tables I know what to do and not to do with autonumbers!

@ssanfu

Thanks, the links actually explained a lot for me, and why I shouldn't use the autonumbers for the purpose I am currently using them for.

[DepricatedZero]

Until the next time you delete a record or Autonumber randomly throws in an out of sequence number. Autonumber is not guaranteed to be sequential, or even positive for that matter, only unique.

fml really? I've got a couple functions that do a quick dirty >0 check on the autonumbered ID. Guess I'll have to get on fixing that. THANK YOU! Hahaha, did NOT know it would throw negatives randomly, I thought it always counted up sequentially

[Beetle]

I thought it always counted up sequentially

I usually does, but you cannot guarantee that it will always do that. It will on occasion spit out a number that doesn't follow the sequence or could even be negative.

[Rawb]

I agree with just about everything everyone here said about autonumbers. If at all possible, don't use them as anything other than their intended purpose as a unique ID (that's not necessarily consecutive, contiguous, or positive).

That said, If you DO use autonumber fields and need to change their value, you can do so with these Functions. Just be EXTREMELY CAREFUL! Screwing up an autonumber field can make your database unusable!

[HvitrEbrithil]

If I were to use a normal number-field to count upwards for each new entry, is it possible to make access do this for me? Just like the autnumbers?

[Beetle]

Yes, but as we discussed in your other thread about toggling Yes/No fields, this would be done at the form level (damn! there's that form thing again ). The more you get into your application, the more you'll realize that you need to use forms for data entry. Tables are for storage of raw data only. They are not designed for data entry/manipulation, that's what forms are for.

[HvitrEbrithil]

Damnit, it looks like I'll have to surrender soon.. Thanks a lot for all of your feedback, people! You'll probably see more questions from me in the future haha

[Rawb]

It could be done at the Query level, but that's essentially the same as the Form level.

SELECT MAX([MyField])+1 AS [MyNextNumber] FROM MyTable;

Or you could use *shudder* the DMax() Function. It would work in Queries, Forms, Reports, VBA code, etc.