Here is code I use to create custom unique ID that reinitializes each year. Your condition for the change of year will be different.
Code:
Public Function NewSample() As String
Dim strLabNum As String
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
CurrentDb.Execute "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
strLabNum = Nz(DMax("LabNum", "Submit"), "")
If strLabNum = "" Then
'this accommodates very first generated number of blank database
strLabNum = Year(Date) & "A-0001"
Else
'this accommodates change in year
If Left(strLabNum, 4) = CStr(Year(Date)) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = Year(Date) & "A-0001"
End If
End If
CurrentDb.Execute "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
End Function
I have never tried to increment an alpha. Think will have to use Asc and Chr functions. Asc will return the ASCII code of character, increment the code, then convert to letter with Chr.
Example: Chr(Asc("A")+1)
Chr(Asc(Left([ID],1))+1)