autonumber adding in middle of sequence

[suepowell]

HI

I have a table in Access 2007 with an autonumber field as it's primary key.

Because of some over enthusiastic tidying up some records were deleted.

I have appended these records from an old version of the table, but I now have a problem.

When I add a new record, through the form or directly into the table it picks a number which is part way through the sequence, probably one I imported as the number to add one on to.

As you can imagine this leads to duplicate numbers for the key which it won't allow.

I didn't think you could re-use numbers lost from an autonumber field, so how can I tell it to go to the highest number and move on from there.

Thanks

Sue

 

[namliam]

If you compact your database the number will reset

Not sure where to find it in 2007, but in older versions Tools> Database Utilities> Compact

Good luck

 

[gemma-the-husky]

reseed your table

use an append query to insert a new record with a specific number for the autonumber that wont cause problems later

you can delete this dummy record later, after new ite,ms have been added to the table