Auto number - renumbering

[tcommis]

Hello all - I've done this before but for some reason it doesn't seem to be working now.
I have a table with auto number as the primary key. In testing my database i made up stuff to test and am now ready to begin using it. the problem is I would like the auto numbers to be in sequence and not jump around from the deleted records. I compacted the database but that didn't work. what am i missing?

 

[jeremie_ingram]

You could remove the primary key settings, then delete the field. Save/close the table, then reopen it. Add the field back in, and reset it to the primary key. This should work.

 

[tcommis]

Worked like a charm! God, some time the simplest things makes you feel like a dope.
Thanks again.

 

[kidzmom3]

The best way I've found to do it is:
1.) create another table, i.e. tblAutoNo with just one field, a number field-long integer and name it the same as your autonumber field in the originating table.
2.) Switch to datasheet view and enter a value in the number field that is 1 less than the number you want the autonumber field to start with.
3.)Create and run a append query to append tblAutoNo to originating table
One note if the AutoNumber field in the originating table is the primary key, remove that status temporarily before running the query, then you can reset it to primary key.
In this way you can reset your autonumbering at 1 and it will fill in any missing numbers or gaps as it goes!