Start New Discussion within our Software Development Community
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

Is it possible to generate a seven digit ID from a name of a customer in such a way that the first three digits are alphabetic (say an acronym of the initials of the customer) and the last four digits are numeric using some logic that uniquely identifies that customer? Say, if the customer name is Vikram Shankar Mathur, I get the ID VSM8819? The problem is if there is a Vivek Shankar Mathur, it should reflect as another unique ID like VSM8820.

Please help.

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.