Increment letters VBA (1 Viewer)

[arnelgp]

you are right, then modify the function, with or without pk this should work.

Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000")
    c = DMax("Len(autoGen) & autoGen", "theTable", "Left(autoGen,3)='" & julian & "'") & ""
    If c = "" Then
        c = "a"
    Else
        c = Mid(c, InStr(c, julian))
        c = Dec2Alpha(Alpha2Dec(Replace(c, julian, "")) + 1)
    End If
    NextSeries = julian & c
End Function

 

[Galaxiom]

This will only work if the table has a primary key like autonumber, one that is always ascending. theAutoGenField cannot be the primary key of the table.

Best not rely on autonumber to be anything that matters. Numbers in the sequence can be skipped under some circumstances.