Autonumber Skips

[Helpme]

I am using Autonumber to assign project numbers with a format 07-####, every
once and a while it will skip a number example 07-1030 then it skips to
07-1032. How can i go back to assign the missing number?

 

[John W. Vinson]

I am using Autonumber to assign project numbers with a format 07-####, every
once and a while it will skip a number example 07-1030 then it skips to
07-1032. How can i go back to assign the missing number?

You can't.

Autonumbers are NOT suitable for this purpose. An Autonumber has one
purpose, and one purpose only: to provide an almost-guaranteed (there
are bugs) unique key. They are NOT guaranteed to be gapless and can
even become random (say if you Replicate your database).

If you're getting the 07 from looking at today's date, that will also
change to 08 next January - renumbering all your projects!

Instead, I'd recommend using VBA code to store this data in a Text
field. I have code from the Access Developer's Handbook to do this,
but it's copyrighted and I'm reluctant to post it for that reason.
Basically, you need a table with one record, for the next available
sequential number; the code would open this table exclusively,
determine the value, increment it, store it back into the table, and
return the retrieved value back to your Form to update the project
number.

John W. Vinson [MVP]

 

[Al Campagna]

Most probably some started to create a record for 1031, and Esc'd out without saving.
Access considered 1031 as previously used.
AutoNumbers are not "guaranteed" to be contiguous.
If you must have contiguity, you can create your own autonumnber by incrementing a
value by one on each new record.
For ex. MyID DefaultValue would be...
= NZ(DMax("[MyID]", "tblMyTable") +1
If some one "backs out" of a new record, that next Max number +1 is not stored, so the
next Ned record will re-interrogate the table anew to create the needed number portion.
I assume you concatenate "07-" & MyID "on the fly" to set your your project number.
Make sure MyID (hidden is best) and ProjectNo are both disabled &
locked... with NO user access.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Jerry Whittle]

Two things that I've learned to appreciate in Oracle and wish Access had:
Sequences and Triggers.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

If you must have contiguity, you can create your own autonumnber by incrementing a
value by one on each new record.
For ex. MyID DefaultValue would be...
= NZ(DMax("[MyID]", "tblMyTable") +1

Have you considered how inserting a large value, say 2147483647, could
be a problem? A better approach could be to have a permanent auxiliary
Sequence table of integers to which a sequence in a working table can
be joined to find the minimum unused sequence value.

Jamie.