Mega Code Archive
Changing the Value of an AutoNumber
<![CDATA[
Sub ChangeAutoNumber()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim beginNum As Integer
Dim stepNum As Integer
Set conn = New ADODB.Connection
conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\mydb.mdb"
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Open "Shippers", conn
.MoveLast
End With
beginNum = rst(0)
rst.MovePrevious
stepNum = beginNum - rst(0)
MsgBox "Last Auto Number Value = " & beginNum & vbCr & _
"Current Step Value = " & stepNum, vbInformation, _
"AutoNumber"
rst.Close
conn.Close
Set conn = Nothing
End Sub
]]>