Set AutoNumbers to start from ...

        3 votes: *****     9,468 views      No comments
by Allen Browne, 20 April 2005    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne,

Set AutoNumbers to start from ...

Resetting an AutoNumber to 1 is easy: delete the records, and compact the database.

But how do you force an AutoNumber to start from a specified value? The trick is to import a record with one less than the desired number, and then delete it. The following sub performs that operation. For example, to force table "tblClient" to begin numbering from 7500, enter:

    Call SetAutoNumber("tblClient", 7500)

Sub SetAutoNumber(sTable As String, ByVal lNum As Long)
On Error GoTo Err_SetAutoNumber
    ' Purpose:      set the AutoNumber field in sTable to begin at lNum.
    ' Arguments:    sTable = name of table to modify.
    '               lNum = the number you wish to begin from.
    ' Sample use:   Call SetAutoNumber("tblInvoice", 1000)
    Dim db As DAO.Database      ' Current db.
    Dim tdf As DAO.TableDef     ' TableDef of sTable.
    Dim i As Integer            ' Loop counter
    Dim fld As DAO.Field        ' Field of sTable.
    Dim sFieldName As String    ' Name of the AutoNumber field.
    Dim vMaxID As Variant       ' Current Maximum AutoNumber value.
    Dim sSQL As String          ' Append/Delete query string.
    Dim sMsg As String          ' MsgBox string.

    lNum = lNum - 1             ' Assign to 1 less than desired value.

    ' Locate the auto-incrementing field for this table.
    Set db = CurrentDb()
    Set tdf = db.TableDefs(sTable)
    For i = 0 To tdf.Fields.Count - 1
        Set fld = tdf.Fields(i)
        If fld.Attributes And dbAutoIncrField Then
            sFieldName =
            Exit For
        End If

    If Len(sFieldName) = 0 Then
        sMsg = "No AutoNumber field found in table """ & sTable & """."
        MsgBox sMsg, vbInformation, "Cannot set AutoNumber"
        vMaxID = DMax(sFieldName, sTable)
        If IsNull(vMaxID) Then vMaxID = 0
        If vMaxID >= lNum Then
            sMsg = "Supply a larger number. """ & sTable & "." & _
                sFieldName & """ already contains the value " & vMaxID
            MsgBox sMsg, vbInformation, "Too low."
            ' Insert and delete the record.
            sSQL = "INSERT INTO " & sTable & " ([" & sFieldName & "]) SELECT " & lNum & " AS lNum;"
            db.Execute sSQL, dbFailOnError
            sSQL = "DELETE FROM " & sTable & " WHERE " & sFieldName & " = " & lNum & ";"
            db.Execute sSQL, dbFailOnError
        End If
    End If
    Exit Sub

    MsgBox "Error " & Err.Number & ": " & Err.Description, , "SetAutoNumber()"
    Resume Exit_SetAutoNumber
End Sub

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Set AutoNumbers to start from ...'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).