Data type: Autonumber

B

bxyle

Will there be a way to use the skipped numbers? My ID to which the data type
is set to "Autonumber" starts from 1~6 and it jumped to 13? 7~12 is nowhere,
how can I assign data on these numbers?
 
K

Ken Snell [MVP]

You cannot. An autonumber should not be used if the value is to be
sequential without gaps; autonumbers use values whenever a new record
starts, even if you cancel that record before you save it.

If you need sequential numbers without gaps, you'll need to use a customized
"programming" solution that assigns the next number for the sequence and
tracks which ones have been used, etc. There are many "solutions" available
for how to do this; which one works best for you depends upon how your
database's setup and needs.

In general, one easy way to do this is this:

NextNumber= Nz(DMax("NumberFieldInTable", "TableName"), 0) + 1
 
B

bxyle

ty.

Ken Snell said:
You cannot. An autonumber should not be used if the value is to be
sequential without gaps; autonumbers use values whenever a new record
starts, even if you cancel that record before you save it.

If you need sequential numbers without gaps, you'll need to use a customized
"programming" solution that assigns the next number for the sequence and
tracks which ones have been used, etc. There are many "solutions" available
for how to do this; which one works best for you depends upon how your
database's setup and needs.

In general, one easy way to do this is this:

NextNumber= Nz(DMax("NumberFieldInTable", "TableName"), 0) + 1
Click to expand...
 
P

peregenem

Ken said:
You cannot.
Click to expand...

You can.

Here is some code from the Immediate Window which:

· Which creates a table with a autonumber key_col.
· Auto-generates key_col=1.
· Starts to autogenerate key_col=2 but rolls back the transaction so
that key_col=2 is not actually created
· Creates a row key_col=3.
· Shows that key_col=2 was not autogenerated.
· Creates key_col=2.
· Shows that key_col=2 was created.

CurrentProject.Execute "CREATE TABLE Test (" & _
"key_col INTEGER IDENTITY(1,1) NOT NULL," & _
" data_col INTEGER NOT NULL UNIQUE)"

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (1)"

CurrentProject.BeginTrans

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (2)"

CurrentProject.RollbackTrans

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (3)"

set rs = CurrentProject.Execute("SELECT key_col, data_col FROM Test")

? rs.GetString
1 1
3 3

CurrentProject.Execute "INSERT INTO Test (key_col, data_col) VALUES (2,
2)"

set rs = CurrentProject.Execute("SELECT key_col, data_col FROM Test")

? rs.GetString
1 1
3 3
2 2
 
K

Ken Snell [MVP]

I was not saying that it was impossible to "reuse" missing numbers -- what I
was saying is that autonumber field itself will not reuse those numbers.

Yes, you can "insert" specific values for the autonumber field into new
records being inserted into the table. However, to identify what numbers are
"missing" will require additional code or tables where such gaps are
identified/tracked. The amount of programming needed for that often is more
complex than what most users seek for a "simple" setup to assign the next
sequential number if not using an autonumber.
--

Ken Snell
<MS ACCESS MVP>


You cannot.
Click to expand...

You can.

Here is some code from the Immediate Window which:

· Which creates a table with a autonumber key_col.
· Auto-generates key_col=1.
· Starts to autogenerate key_col=2 but rolls back the transaction so
that key_col=2 is not actually created
· Creates a row key_col=3.
· Shows that key_col=2 was not autogenerated.
· Creates key_col=2.
· Shows that key_col=2 was created.

CurrentProject.Execute "CREATE TABLE Test (" & _
"key_col INTEGER IDENTITY(1,1) NOT NULL," & _
" data_col INTEGER NOT NULL UNIQUE)"

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (1)"

CurrentProject.BeginTrans

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (2)"

CurrentProject.RollbackTrans

CurrentProject.Execute "INSERT INTO Test (data_col) VALUES (3)"

set rs = CurrentProject.Execute("SELECT key_col, data_col FROM Test")

? rs.GetString
1 1
3 3

CurrentProject.Execute "INSERT INTO Test (key_col, data_col) VALUES (2,
2)"

set rs = CurrentProject.Execute("SELECT key_col, data_col FROM Test")

? rs.GetString
1 1
3 3
2 2
 
Top