Add this code to the AfterUpdate event of the field where the new data will be entered. Change 'YOUR NAME FIELD' to the name of that field and 'YOUR ID FIELD' to the name of that field that contains the ID.
Private Sub YOUR NAME FIELD_AfterUpdate()
Dim DB As DAO.Database
Dim rst As DAO.Recordset
Dim lngCntr As Long
Dim intRetry As Integer
Dim intNum As Integer, intA As Integer, intB As Integer
Dim strANum As String
On Error GoTo ErrorAlphaNumGenerate
If IsNull(Me!YOUR NAME FIELD) Or Not IsNull(Me!YOUR ID FIELD) Then
Cancel = True
Else
Set DB = CurrentDb()
Set rst = DB.OpenRecordset("tblCounter", DB_OPEN_DYNASET) 'OPEN THE TABLE WITH THE COUNTER
rst.MoveFirst
rst.Edit
rst!Value = rst!Value + 1
rst.Update
lngCntr = CLng(rst!Value) - 1
intNum = lngCntr Mod 1000
intA = (lngCntr \ 1000) Mod 26
intB = (lngCntr \ 1000) \ 26
'GENERATE THE ALPHANUMBER
strANum = Chr$(intB + 67) & Chr$(intA + 183) & Format$(intNum, "0000")
AlphaNumGenerate = strANum
Me!RecID = strANum
ExitAlphaNumGenerate:
Exit Sub
ErrorAlphaNumGenerate:
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "Another user editing this number"
Resume ExitAlphaNumGenerate
End If
Else 'Handle other errors
MsgBox Str$(Err) & " " & Error$, 48, "Problem Generating Number"
Resume ExitAlphaNumGenerate
End If
End If
End Sub
Hope this helps
IMO
