Make an Auto-Sequence Function for an Alpha-Numeric String

[Kumba1]

I am doing an order-entry system for my boat shop... All my orders are a 5-digit number (like 12345)... all my warranty's are pre-fixed with a WC... is there a way I can auto-generate the next available Warranty Number? IE, If the last Warranty Number is WC10234, the next available number would be WC10235... and then WC10236... Any help would be appreciated... Thanks

 

[mp9]

I've assumed a table called tblWarranty and a field within that called WarrantyNumber - if your objects have different names you'll have to modify the code accordingly.

Also, all this example does is work out the next available WC number and assign in to a string variable, but it should get you on the right track.

Dim strWC As String
strWC = "WC" & Format(CLng(Mid(DMax("WarrantyNumber", "tblWarranty"), 3)) + 1, "00000")

Hope this is useful.

http://www22.brinkster.com/accessory/

 

[Kumba1]

That is Exactly what I was looking for... Many Thanks to you sir

 

[MichaelRed]

Public Function basIncrStr(strIn As String) As String
        
    Dim MyChrs() As String
    Dim strTemp As String
    Dim Idx As Integer
    Dim MyChr As String * 1
    Dim MaxChr As String * 1
    Dim CarryChr As String * 1
    Dim blnCryFlg As Boolean

    ReDim MyChrs(Len(strIn))

    Idx = 1
    Do While Idx <= Len(strIn)
        MyChrs(Idx - 1) = Mid(strIn, Idx, 1)
        Idx = Idx + 1
    Loop

    Idx = UBound(MyChrs) - 1
    Do While Idx > 0

        MyChr = MyChrs(Idx)
        Select Case IsNumeric(MyChr)
            Case Is = True              'Numeric thinggy 0 : 9
                MaxChr = &quot;9&quot;
                CarryChr = &quot;0&quot;

            Case Is = False             'Alpha | Punctuation | ...
                MaxChr = &quot;Z&quot;
                CarryChr = &quot;A&quot;
                If (Not (UCase(MyChr) >= &quot;A&quot; And UCase(MyChr) <= &quot;Z&quot;)) Then
                    'OOPs = We have some Punctuation / White space to Deal w/
                    MyChrs(Idx) = MyChr
                    GoTo NxtChr
                End If

        End Select

        If (MyChr < MaxChr) Then
            'Found a char not at limit.  Process
            MyChrs(Idx) = Chr((Asc(MyChrs(Idx)) + 1))
            Exit Do
         Else
            MyChrs(Idx) = CarryChr
            blnCryFlg = True
        End If
NxtChr:
        Idx = Idx - 1
    Loop

    Idx = 0
    Do While Idx < UBound(MyChrs)
        strTemp = strTemp & MyChrs(Idx)
        Idx = Idx + 1
    Loop

    basIncrStr = strTemp
End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Kumba1]

Wow... that's a lil above my head, but it seems to work nicely Thanks mike