Have Access 2000. Have a table with 22 records. First field is autonumber and I've made it a Key Field. After record 19 I deleted a few records but was surprised the next autonumber came up as 22. But I wanted it to be 20. So I deleted that record. Next autonumber comes up as 23. So I search in Help, but can find nothing on re-ordering all the autonumber fields so they will read 19,20,21,22. How do I fix my problem. Thank You.
Sincerely, Pat Crotty

There isn't a straight forward way of doing this, but the following should work for you...

1. Open the table in design view
2. Delete the Autonumber field
3. Save the table
4. You may need to exit and restart Access at this point
5. Open the table in design view
6. Add the field, set it to Autonumber
7. Close and Save and you should be good.

Hope that helps,

Mike

Mike
EchoVue.com

This will resolve your immediate problem, but it will keep happening to you every time you delete a record. The autonumber field keeps track of each number it uses in increments of 1 generally, and will not reuse a number it has used already, regardless of whether the record has been deleted or not. It would be programatically difficult to renumber all of your PK's everytime you deleted a record, although referential integrity could be maintained (cascaded updates).

If you need to use the autonumber field to tell you how many records are in your table, you should consider a query to do this for you. i use a query to tell me the number of active records in some of my databases (I don't delete records usually, just select a "retired" check box.)

Other than personal issues, however, I can't think of a reason to change the non-meaningful PK every time a record is removed (yes, non-meaningful as opposed to meaningless.) Is there a compelling design reason for this? Perhaps there is another solution.



mmcdonal