Please help with alphanumeric number handling

[crazytegger]

I'm creating a function that will autoincrement a purchase order number. The number is the logged in users initials followed by four numbers (eg. JC0001). How would I go about determining the highest existing number with the prefix of the person who is creating the new order?

Thanks, Justin

Here is my code, I apologize if it is a bit elementary:

Public Function GeneratePONumber()
    Dim MyInitials, MyName, MyNames
    Dim strFname, strLname, strInitials As String
    Dim intHighPO As Integer
 
    'INITIAL GENERATOR
    'Determines who is logged in, and generates user's initials
    strFname = DLookup("username", "users", "userid = " & intAuthUserId)
    strLname = DLookup("userlastname", "users", "userid = " & intAuthUserId)
    MyNames = Split(strFname & " " & strLname, " ")
    For Each MyName In MyNames
        If Len(MyName) > 0 Then MyInitials = MyInitials & UCase(Left(MyName, 1))
    Next
    'END INITIAL GENERATOR
 
    For Each OrderPONumber In orders
        'FIND HIGHEST PO NUMBER BEGINNING WITH MyInitials
    Next
 
    'ADD INITIALS AND LAST PO NUMBER+1
    strNewPONumber = MyInitials & intHighPO + 1
 
End Function

[Killer42]

I'd suggest you use the Max database function to find the maximum value that is
Like "JC*"
Or depending on the database, the wildcard character might be %, which would make:
Like "JC%"

You might also use a Between clause, such as
Between "JC0001" AND "JC9999"

[lotus18]

Hi

Make a composite key for that, combination of username and another key (set to autonumber)...

[Killer42]

Something I forgot to mention. This scheme looks as though has a fundamental flaw which may cause problems later. That is, the inability to handle multiple people with the same intials.

[9815402440]

hi

following code should help you

Const MyInitials = "JC"
Dim strArr(10) As String
Dim i As Integer
'Fill array
For i = 0 To 9
    strArr(i) = MyInitials & Format(i, "0000#")
Next
Dim intMax As Integer
intMax = 0
'find maximum
For i = 0 To 9
    If Val(Replace(strArr(i), MyInitials, "")) > intMax Then
        intMax = Val(Replace(strArr(i), MyInitials, ""))
    End If
Next
MsgBox MyInitials & Format(intMax + 1, "0000#")
 

regards
manpreet singh dhillon hoshiarpur

[crazytegger]

Thanks for the help. I enjoy seeing how many different ways there are to accomplish the same objective with code. I thought about the composite key method however, I wanted to keep the numbers sequential per user. Also, thanks for pointing out the fundamental flaw, I'm still trying to decide how to address this. I will reply back when I figure it out.

Thanks again,

Justin

[Killer42]

Cool. I'm sure we'll all be interested to hear what direction you take.