Autonumber skipping numbers

[woodlandpearl]

I've noticed that a few weeks ago, that my customer ID number did not match
the actual record number. Upon investigating, I discovered that the
autonumber skipped a number. Has anyone had this problem? This is the
second time around for me. The last time this happened, I had to redo the
numbering sequence. Is there a way to prevent this happening in the future?
None of the records are ever deleted and I'm the only one in the database.
Please advise. Thanks!

 

[Roger Carlson]

The autonumber is not designed to create a sequential numbering scheme.
It's only function is to ensure this number is NEVER duplicated. Therefore,
if you create a new record, but cancel it before it is saved, that
autonumber will be used and never used again. That's how you have developed
gaps.

You should not use the autonumber for this purpose. If you MUST have a
sequential number (and I would argue that you do not in most cases), you can
create one yourself with a little bit of code using the DMax domain
function.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which explains the problem and
illustrates one solution.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[fredg]

I've noticed that a few weeks ago, that my customer ID number did not match
the actual record number. Upon investigating, I discovered that the
autonumber skipped a number. Has anyone had this problem? This is the
second time around for me. The last time this happened, I had to redo the
numbering sequence. Is there a way to prevent this happening in the future?
None of the records are ever deleted and I'm the only one in the database.
Please advise. Thanks!

There is no need for an AutoNumber to be consecutive.
It only needs to be unique.
It may have been that a new record entry was started but then not
completed. That AutoNumber would then have been considered used by
Access and the next completed record would have a 'skipped' number.

If for some reason you need consecutive numbers, you'll need some
other method. The usual method is to use a DMax() +1 function to find
the highest value in the field and add 1 to it when a new record is
added.
This will still leave blanks if, in the future, a record is deleted,
but not if a new record entry is started but then canceled.