I have very little experience using autonumber field. I have come to know that autonumber field may not follow the order of insertion. Is it possible?

To illustrate consider a table (auto-id(autonumber),name)
When the first record is added it contains

1,Arvind
2,Jay
3,Rahul.

So, for m & n, m<n can the mth record created have autoid greater than that of tha nth record created?

Coz it can create some problems with some key reordering that i do with temporary tables having autonumbers.

Thanks in advance.

If the field is so important for your structuring then ask yourself: "why am I using an autonumber to perform this?"

Autonumbers are best used just to give records a unique ID.

Thanks for replying. :)

My reply to ur question is that i had just thought of using a simple method to achieve my objective. I can and probably will use recordsets for the same purpose henceforth but i would still like to know.

Autonumbers can be used (and are) to determine the sequence of record entry. In fact, they are the only reliable method for doing so. The autonumber sequence may have gaps but when the value of one autonumber is greater than another, you can be certain that the record with the higher valued autonumber was inserted AFTER the record with the lower valued autonumber. This of course assumes that the autonumber's NewValues property is Increment. If the autonumber's NewValues property is Random, then there is no way to derive record entry order.

So, for m & n, m<n can the mth record created have autoid greater than that of tha nth record created? - so to answer your question directly, No as long as the NewValues property of the autonumber is Increment.

Thanks a lot Pat. So, i do not need to change my method.

By the way, some digging up on my own has revealed that insertion from another table may cause the gaps to be filled in if u desire. But, as u said keeping a separate untouched field with newvalues to increment is a safe method which is what i wanted to know.

Thanks once again.

There is no reason to attempt to fill the "gaps". Autonumbers are intended to provide a unique identifier for records. They may also be used to determine entry order. They are not the same as record numbers that are supported in older databases or flat file systems.

Although it is important to remember that you can control the assignment of autonumbers by means of an append query, this technique is intended to be used to allow you to convert old tables while maintaining their primary keys.

Thanks a lot Pat, for the info. I understand ur point and it is valid.