Many people operate under a misconception of what an autonumber is. Access (Jet, really, the program that moves data for Access) generates unique, sequential autonumbers when you create records. So people expect that the autonumbers in their records will be sequential. Fair enough. But if you take a look at the behavior of an Access table with an autonumber in it, or read some of the hundreds of posts about this topic, you'll see something very different.

Make a new database with one table with two fields, one an autonumber field and the other a text field. Create a couple of records. Now delete the last record and then create another one. You'll see that Access doesn't reuse that autonumber, it just keeps on going. Now create a record but don't save it. Instead, hit the escape key a couple of times. And then create a new record. You'll see that Access won't use the number that was generated for that most recent record, either.

Autonumbers are a great method for insuring that records are uniquely identified. But they were not designed to count records, and they should never be used for such a purpose. If that's what you're trying to do, get the Access [version number] Developer's Handbook and use the code they've got for generating sequential numbers.

ABCDataworks - 235 Ainslie Street, Suite 3 Brooklyn, NY 11211 - (646) 290-8296