VBA/Excel/Access/Word/Access/Autonumber
Controlling Autonumber Fields
<source lang="vb">
Sub CreateAutonumber()
Dim cmd As ADODB.rumand Set cmd = New ADODB.rumand cmd.ActiveConnection = CurrentProject.Connection cmd.rumandText = "CREATE TABLE Customers2 " & _ "(CustomerID AUTOINCREMENT (100000,1), " & _ "CompanyName TEXT (50), IntroDate DATETIME, " & _ "CreditLimit CURRENCY DEFAULT 5000)" cmd.Execute
End Sub
</source>
Retrieve the last-assigned autonumber value
<source lang="vb">
Sub LastAutonumber()
Dim cmd As ADODB.rumand Dim rst As ADODB.Recordset Set cmd = New ADODB.rumand Set rst = New ADODB.Recordset cmd.ActiveConnection = CurrentProject.Connection cmd.rumandText = "INSERT INTO Customers2 " & _ "(CompanyName, IntroDate, CreditLimit) " & _ "VALUES ("Test Company", #1/1/2007#, 100) " cmd.Execute rst.ActiveConnection = CurrentProject.Connection rst.Open ("SELECT @@Identity as LastCustomer FROM tblCustomers")
End Sub
</source>