Access AutoNumber

[Toot]

Hullo gurus!

Background:
I'm currently using an AutoNumber field in my Access97 DB to assign an ID to each record...most of the time it works fine but every now and again it goes haywire and jumps all over the place before settling back down again. It's *VERY* important that the number is sequential - i.e. user1 presses "Add" and 1 is assigned, user2 adds and gets 2 assigned, user 1 adds and gets 3 assigned, etc. (that's how I had assumed it would work).

Question:
So, I've got to write my own autonumbering system... Does anyone have any clues how to do it? I thought of adding a ClientIDs table with one field and one record... but that seems a rather backward way of doing it - there must be a cleaner way.

Any help *hugely* appreciated.

All the best,
Toot

[Ianpbaker]

The Reason the auto number goes all over the place is because if you delete a record, it ignores any deleted records. To get round this, every time you want to add a new record, use a recorset to grab the last id number, (SELECT idnum FROM tbltest ORDERBY idnum) then move to the last record, add one to it and you have your new record.

As long as the numbers arec already sequential this will work

Hope this helps

Ian

[Toot]

OK That's fine, but how would I guarantee that two requests don't read and alter the number at the same time whilst ensuring users who are just browsing don't get read errors?
I'm using controls bound to an ADO data object.

(do I just begin a transaction before my SELECT and commit after I've changed it????)

Thanks again,
Toot