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