Autonumber in Access (VBA)

Public Function GetNextAutoNumber(strTableName As String) As Long

'Declare some object variables

Dim wsCurrent As DAO.Workspace
Dim DBCurrent As DAO.Database
Dim qdfAutoNum As DAO.QueryDef
Dim rstAutoNumbers As DAO.Recordset

'set up references for those objects

Set wsCurrent = DBEngine.Workspaces(0)
Set DBCurrent = wsCurrent.Databases(0)
Set qdfAutoNum = DBCurrent.QueryDefs("qrybasAutoNumber")
qdfAutoNum.Parameters(0) = strTableName

' Create the recordset

Set rstAutoNumbers = qdfAutoNum.OpenRecordset(dbOpenDynaset)

'Check for empty an empty recordset and proceed with it accordingly

If (rstAutoNumbers.BOF) And (rstAutoNumbers.RecordCount = 0) Then
    GetNextAutoNumber = -1  ' returned if autonumber can not be created.
Else
    rstAutoNumbers.Edit
    rstAutoNumbers!LastNumberUsed = _
    rstAutoNumbers!LastNumberUsed + rstAutoNumbers!Increment
    
'  update the tblAutoNumbers and return the key value

    GetNextAutoNumber = rstAutoNumbers!LastNumberUsed
    rstAutoNumbers.Update
End If

' Clean up objects

rstAutoNumbers.Close
qdfAutoNum.Close
Set rstAutoNumbers = Nothing
Set qdfAutoNum = Nothing

DBCurrent.Close
wsCurrent.Close


End Function