Autonumber Skips

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
Click to expand...

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]
 
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."
 
An Autonumber has one
purpose, and one purpose only: to provide an almost-guaranteed (there
are bugs) unique key.
Click to expand...

I think you give autonumber too much credit <g>. Autonumber *does* one
thing: it generates a number or GUID according to an algorithm
(increment, random, new GUID).

There is nothing about autonumber that guarantees it will be unique.
If you want the value to be unique in a column you have to apply a
UNIQUE constraint, so there's nothing special about autonumber in this
regard. Even GUID is not guaranteed to be unique in all
circumstances!

The purpose for which an autonumber is used is an entirely different
matter. Using an autonumber as a key could be a fallacy if you
consider that a key needs to exist in the reality being modelled and
pretty much everyone says you shouldn't expose an autonumber.

Here's a quick example of how generated autonumber values (ID) can
repeat:

Sub repeating_autonum()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" ID INTEGER IDENTITY(1, 1073741824)" & _
" NOT NULL, insert_sequence" & _
" INTEGER);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (1);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (2);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (3);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (4);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (5);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (6);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (7);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (8);"
.Execute _
"INSERT INTO Test (insert_sequence)" & _
" VALUES (9);"

Dim rs
Set rs = .Execute( _
"SELECT ID, insert_sequence" & _
" FROM Test;")
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
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
Click to expand...

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.

--
 
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.

Jamie Collins said:
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
Click to expand...

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.
Click to expand...
 
Two things that I've learned to appreciate in Oracle and wish Access had:
Sequences and Triggers.
Click to expand...
From a SQL Server perspective, the majority of my triggers are to
Click to expand...
implement table-level constraints, for which in Jet we have table-
level CHECK constraints. So for Jet, rather than triggers, I'd ask
Santa for the ability to execute more than one SQL statement in a
single SQL PROCEDURE plus deferrable constraints.

I find the standard trick of using a Sequence table of integers works
well.

Jamie.

--
 
Back
Top