ACCESS: Can I make Autonumber field start with 582 rather than 1.

[BobW]

I am designing a system for entering tickets. I want it to be auto number.
Problem is the company is at ticket # 3516, and I wish to start the
autonumber field at that point.

 

[Damian S]

Hi Bob,

You will need to enter 3515 records into your table, delete all bar record
number 3515, compact and repair, then delete 3515. The next record (as long
as you don't do another compact/repair) will be 3516.

If you don't know how to enter 3515 records, you could use a for loop in a
code module to execute an insert sql statement.

Hope this helps.

Damian.

 

[Jason Lepack]

I created this procedure for resetting autonumbers.

I would like to mention however that autonumbers are not necessarily
always consecutive. If you start to add a record and cancel it then
the next record you attempt to add will have the next autonumber in
the sequence.

You need to add the Reference:
"Microsoft ADO Ext. 2.8 for DDL and Security"

' *******************************************************************
' resestSeed - resets an autonumber field
' *******************************************************************
' tableName - the table that contains the autonumber
' fieldName - the field that is the autonumber
' seedValue - where you want the new autonumber to start
' *******************************************************************
Public Sub resetSeed(tableName As String, fieldName As String,
seedValue As Integer)
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
cat.Tables(tableName).Columns(fieldName).Properties("Seed") =
seedValue
End Sub

Cheers,
Jason Lepack

 

[tina]

you can also set the Autonumber with an Append query. just append the number
3515 (and dummy data into any other required fields in the table) into the
table, then delete it. note that if you compact the database *before*
entering a "real" record, the Autonumber will be reset back to one again.

as Jason mentioned, the Autonumber is not a reliable source for consecutive
numbers, and should not be used for the purpose you intend. recommend you
generate the ticket number programmatically instead.

hth

 

[BruceM]

This link shows one way of creating a sequential number, which is probably
what you want.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

With autonumber, if a user goes to a new record, then exits that record
before entering data, the autonumber that would have been assigned to that
record is skipped, creating a gap in the sequence. It is almost inevitable
that this will happen.

 

[BobW]

Hi Bob,

You will need to enter 3515 records into your table, delete all bar record
number 3515, compact and repair, then delete 3515. The next record (as long
as you don't do another compact/repair) will be 3516.

If you don't know how to enter 3515 records, you could use a for loop in a
code module to execute an insert sql statement.

Hope this helps.

Damian.

Thank you very much......

 

[BobW]

I created this procedure for resetting autonumbers.

I would like to mention however that autonumbers are not necessarily
always consecutive. If you start to add a record and cancel it then
the next record you attempt to add will have the next autonumber in
the sequence.

You need to add the Reference:
"Microsoft ADO Ext. 2.8 for DDL and Security"

' *******************************************************************
' resestSeed - resets an autonumber field
' *******************************************************************
' tableName - the table that contains the autonumber
' fieldName - the field that is the autonumber
' seedValue - where you want the new autonumber to start
' *******************************************************************
Public Sub resetSeed(tableName As String, fieldName As String,
seedValue As Integer)
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
cat.Tables(tableName).Columns(fieldName).Properties("Seed") =
seedValue
End Sub

Cheers,
Jason Lepack

Thank you very much...

 

[BobW]

Good point. Also note you can explicitly INSERT values into an
autonumber column that may affect the sequence.


You can alternatively use ADODB (to which there's a reference by
default <g>) and SQL DDL e.g. (aircode):

CurrentProject.Connection.Execute _
"CREATE TABLE Test (" & _
" ID INTEGER IDENTITY(3516, 1) NOT NULL UNIQUE," & _
" data_col INTEGER);"

Jamie.

Thank you very much...

 

[BobW]

you can also set the Autonumber with an Append query. just append the number
3515 (and dummy data into any other required fields in the table) into the
table, then delete it. note that if you compact the database *before*
entering a "real" record, the Autonumber will be reset back to one again.

as Jason mentioned, the Autonumber is not a reliable source for consecutive
numbers, and should not be used for the purpose you intend. recommend you
generate the ticket number programmatically instead.

hth

Thank you very much...

 

[BobW]

This link shows one way of creating a sequential number, which is probably
what you want.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

With autonumber, if a user goes to a new record, then exits that record
before entering data, the autonumber that would have been assigned to that
record is skipped, creating a gap in the sequence. It is almost inevitable
that this will happen.

Thnak you very much...

 

[Jamie Collins]

This link shows one way of creating a sequential number, which is probably
what you want.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

"Form1 illustrates how to use the DMax function to simulate the action of
the Autonumber field."

That doesn't sound correct. If you explicitly insert the maximum INTEGER
value into an autonumber column it doesn't interrupt the generated sequence
but would cause a DMAX+1 algorithm to error, so that can't be the correct
algorithm for autonumber.

As a hint of how autonumber actually works in Jet, plus why an autonumber
column should perhaps always be constrained as UNIQUE, try the following VBA
code which uses large (and significant) seed values:

Sub autonum_test()
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 Test1 (" & _
" ID1 INTEGER IDENTITY(1, 1073741824)" & _
" NOT NULL, insert_sequence" & _
" INTEGER);"
.Execute _
"CREATE TABLE Test2 (" & _
" ID2 INTEGER IDENTITY(1, 1073741825)" & _
" NOT NULL, insert_sequence" & _
" INTEGER);"
.Execute _
"CREATE TABLE Digits (nbr INTEGER);"
.Execute _
"INSERT INTO Digits VALUES (0);"
.Execute _
"INSERT INTO Digits (nbr) SELECT DT1.nbr" & _
" FROM (SELECT 1 AS nbr FROM Digits UNION" & _
" ALL SELECT 2 FROM Digits UNION ALL SELECT" & _
" 3 FROM Digits UNION ALL SELECT 4 FROM Digits" & _
" UNION ALL SELECT 5 FROM Digits UNION ALL" & _
" SELECT 6 FROM Digits UNION ALL SELECT 7" & _
" FROM Digits UNION ALL SELECT 8 FROM Digits" & _
" UNION ALL SELECT 9 FROM Digits ) AS DT1;"
.Execute _
"INSERT INTO Test1 (insert_sequence) SELECT" & _
" nbr FROM Digits;"
.Execute _
"INSERT INTO Test2 (insert_sequence) SELECT" & _
" nbr FROM Digits;"
Dim rs
Set rs = .Execute( _
"SELECT DISTINCT 'insert_sequence'," & _
" 'autonumber_repeats', 'autonumber_wraps' FROM" & _
" Test1 AS T1" & vbCr & "UNION ALL SELECT" & _
" T1.insert_sequence & CHR(9)," & _
" T1.ID1 & STRING(IIF(LEN(CSTR(T1.ID1))" & _
" < 6, 2, 1), CHR(9)), T2.ID2 FROM" & _
" Test1 AS T1, Test2 AS T2 WHERE" & _
" T1.insert_sequence= T2.insert_sequence;")

MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[tina]

did you look at the example database? it doesn't use an Autonumber field,
but rather a Long Integer field. the point of the code, and the example, is
to assign a sequential number programmatically so the user doesn't have to
do it manually.

hth

 

[Mirek]

BobW je napisao/la:

I am designing a system for entering tickets. I want it to be auto number.
Problem is the company is at ticket # 3516, and I wish to start the
autonumber field at that point.

SET AUTONUMBER START VALUE:

0) Close all tables that you are working on

1) Press Ctrl-G

2) In Immediate Window enter command:

CurrentProject.Connection.Execute "ALTER TABLE [Table1] ALTER
COLUMN [ColumnID] IDENTITY(3516, 1)"

3) Press Enter

4) If everything goes fine no message will be shown (like nothing've
happened)

5) To test it insert new row in altered table and watch for autonumber
column value