Creating an automatic custom alphanumeric number?

[rodvaN]

Hello.. I got a form with a click button, when I click it I want it to generate a number like "AAA0900001" the 09 number means the current year, and the 00001 number is the counter +1
When it generates, place it on a field on the new form.
Thanks in advance.

 

[ezfriend]

I am not sure what the purpose of the "AAAYY" is for, but here is how I would do it. It's probably not the best way to do it, but just my two cents.

Private Function GetNextValue() As String

    Dim sSQL As String
    Dim rs As Recordset
    
    sSQL = ""
    sSQL = sSQL & "SELECT MAX(AutoAlpha) FROM Table1 "    'Table1 should be the table that stores your data and the AutoAlpha is the primary field that you need the auto number

    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    If Right(rs(0), Len(rs(0)) - 5) < 1000000 Then
        GetNextValue = "AAA" & Year(Date) & GetLeadingZeroes(Right(rs(0), Len(rs(0)) - 5))
    Else
        GetNextValue = "AAA" & Year(Date) & Right(rs(0), Len(rs(0)) - 5)
    End If
    
    rs.Close
    Set rs = Nothing
    
End Function

Private Function GetLeadingZeroes(ByVal lNum As Long) As String

    Dim sVal As String
    
    sVal = lNum + 1
    
    'Here is just keeping track of the 8-digit number by adding zeroes for those number that are less than 8 digit.
    Do While Len(sVal) < 8
        sVal = "0" & sVal
    Loop

    GetLeadingZeroes = sVal

End Function

 

[rodvaN]

Thanks alot for your kind help ezfriend.
Is it possible that you could make an example file?.. if not dont worry Ill figure it out.. newbie in VBA

 

[ezfriend]

Sample database attached.

 

[rodvaN]

Clever..
You saved my week.. Literally.
Thank You ezfriend.