Autonumbering

  • Thread starter Thread starter Damien
  • Start date Start date
D

Damien

Hi

My database table is setup for autonumbering the records,
but randomly, it skips a number of a couple of numbers, so
the record total might be 100 but the next record number
might be 105, so when I look back through the records I
find that say 5, 20, 44, 80 and 90 are missing.

Is there any reason why this might happen and have you any
solutions you could offer.

Regards
Damien
 
Yes. If you start adding a new Record (a value for the
AutoNumber Field is allocated) and then cancel the adding
half wat through, the value allocated will not be
recovered and you have gaps in the AutoNumber Field values.

Note that the *only* purpose of AutoNumber Field is to
provide uniqueness to each Record in the Table. The
AutoNumber Field values will develop gaps (as above) and
can even become (pseudo-)random or even negative. In
fact, users (at least my users) don't see the values of
the AutoNumber Field.

If you care about the value allocated to the Field for
each Record, you should NOT use the AutoNumber Field.
Check Google for "custom number sequence" as this question
comes up fairly regularly in these newsgroups.

HTH
Van T. Dinh
MVP (Access)
 
The autonumber is determined by the last number in
memory. Like the other reply mentioned, if you start to
add a record and then cancel that autonumber has already
been placed into memory. That is why it skips for the
next record.
But if you know when this happens you can still use the
very next number, but you will have to compact the
database. Compacting will remove that canceled record
and reset the autonumber to the next number according to
your table.
 
You should be able to prevent the skipping of record
numbers by using a macro to generate new numbers. You do
this by calling a function (DMax) that finds the previous
highest record number and adds one to that number. If you
cancel before completing the record, the DMax function
returns a special Null value. However, I must admit that
I am an Access novice and though I read the above in an
Access 97 reference book, I have been unable to get it to
work for me. I tried the macro with a data type of auto
number. Maybe I should have tried it with a different
data type. Good luck!
 
Back
Top