Klatuu said:
That would be in inappropriate use of an Autonumber.
I fully agree with the concept it should be two fields, but the use of
autonumber fields should be limited to artificial or surrogate keys and
should never be seen by a user.
Even if the above were not good practice, you cannot depend on autonumbers
to stay sequential. There will be gaps. Any time you start a new record,
but cancel the update, the number is discarded.
If you need truely sequential numbers with no gaps, the better practice
would be to use a DMax function to return the highest current number in the
table and add 1 to it. There are multiple techniques for doing this. The
main area you have to be aware of is handling this in a multi-user
situation.
Interesting comments on Autonumbers, and indeed, the prevailing wisdom,
but I'm not convinced they have only one use. Klatuu is quite right to
point out that there can be gaps, but this needn't be a problem if it's
understood. These things look like numbers, but they don't support most
arithmetic operations beyond identity comparison and magnitude
comparison - a so-called "ordinal" scale. It doesn't make sense to
increment them, let alone add, subtract, multiply or divide them - just
like account "numbers". They provide an effortless unique identifier,
and are widely used for primary keys, but I don't see why they can't be
used for other purposes if their limitations (as well as their
strengths) are understood. Is there any reason why a table couldn't
have two autonumber fields? Why shouldn't they ever be seen? Happy to
be put straight on this - I'm no guru.
Phil
