How Autonumber handles deleted records

[Elizabeth]

I have an Autonumber field in a table for automatically
identifying each new entry. In the data entry form, if I
decide I really didn't want to add a new record, it can
be deleted, but the Autonumber still increments. This
isn't a problem if I actually saved the record and
deleted it later, but how do you stop it from
incrementing when you don't want it to? Or should I not
be using autonumber?

Thanks in advance,
Elizabeth

 

[Peter van der Goes]

I have an Autonumber field in a table for automatically
identifying each new entry. In the data entry form, if I
decide I really didn't want to add a new record, it can
be deleted, but the Autonumber still increments. This
isn't a problem if I actually saved the record and
deleted it later, but how do you stop it from
incrementing when you don't want it to? Or should I not
be using autonumber?

Thanks in advance,
Elizabeth

This is standard behavior for automatically generated key values in Access
and in other relational databases, such as Oracle. The upside is the
guarantee of uniqueness with no work on the developer's part. The downside
is that key values are "wasted" when a transaction is rolled back, or any
other time an autonumber key is issued and then not actually used.
Unless you *need* a sequence of numbers with no gaps the behavior of
autonumber should not pose any problems. If your numbers *must* be issued
with no breaks in the sequence, then autonumber is not for you, and you'll
have to code a number issuing algorithm in your maintenance application that
will disallow skipped numbers.

 

[Elizabeth]

Hum, I was afraid of that, Peter! Technically, I suppose
I wouldn't *mind* having missing numbers, but from
experience, I know that whenever somebody sees a gap in
numbers they automatically wonder "Where are records 10-
20, have they been lost?!" Okay, perhaps it's just ME
(the perfectionist) who gets suspicious! Should I set up
this algorithm you mentioned, and if so, how? Would it be
easier to set up a "dummy" table to store information
temorarily until they hit "save", in which case I guess
you'd need two tables and an Update Query (pain!) Or
store values to memory until Save is pressed? Or, in your
experience, is it not worth worrying about?
Ta.

 

[Peter van der Goes]

Hum, I was afraid of that, Peter! Technically, I suppose
I wouldn't *mind* having missing numbers, but from
experience, I know that whenever somebody sees a gap in
numbers they automatically wonder "Where are records 10-
20, have they been lost?!" Okay, perhaps it's just ME
(the perfectionist) who gets suspicious! Should I set up
this algorithm you mentioned, and if so, how? Would it be
easier to set up a "dummy" table to store information
temorarily until they hit "save", in which case I guess
you'd need two tables and an Update Query (pain!) Or
store values to memory until Save is pressed? Or, in your
experience, is it not worth worrying about?
Ta.

The generated autonumber doesn't represent real data, correct? The approach
I've always used is to simply not show it to the user. That way, the
question never comes up