Alpha-numerical field, with the number section incrementing 1

[Wantok52]

I am setting up a date base for customer's assets and want to use an alpha-numeerical numbering system so that the number part will increment automatically. The alpha part will change according tp the customer eg MARD0001 for one customer and ARAD0001 for an other. I also need the leading zeros to be shown in the table. Can anyone help me with this?

 

[MichaelRed]

Public Function basIncrAlphN(strAlphN As String) As String

    'Michael Red.   12/21/2002
    'To Create an Incrementing A-N field with rollover
    'AAA01 to AAA99
    'AAB01 to AAB99

    Dim Idx As Integer
    Dim MyChr As String * 1
    Dim MyValStr As String
    Dim MyStrStr As String
    Dim MyValVal As Long

    'Easy on me, make user supply the last value.  "I" don't know where it is

    For Idx = 1 To Len(strAlphN)
        MyChr = Mid(strAlphN, Idx, 1)
        If (IsNumeric(MyChr)) Then
            MyValStr = MyValStr & MyChr
         Else
            MyStrStr = MyStrStr & MyChr
        End If
    Next Idx

    MyValVal = CLng(MyValStr) + 1
    If (MyValVal = 100) Then
        'Do The Rollover
        MyValVal = 1

        'Incr the String Part
        'Just put each char into Seperate Char
        MyStrStr = basIncrStr(MyStrStr)

    End If
    
    basIncrAlphN = MyStrStr & right("00" & Trim(str(MyValVal)), 2)

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Wantok52]

Thanks for your reply Michael, but I'm not sure where I put the code, and how I get it to set up the field in a table. Can you help me with this?

Marcus

 

[MichaelRed]

hmmmmmmmmmmm,

w/o knowing more than I need to know about the overall app, I am sure that I can only provide quite generic commentary on the 5W's part. Generally, you would need a new "value" whenever a new record is entered, and would need to provide the 'last' (previous) value in the series. I suppose that the new value would be generated for each record as it was entered for the "primary" record in a related set of records, perhaps in a before update event of a form, so AN example might be:

Private Sub Form_Update()

[tab]Dim MyNewId as String * 9

[tab]MyNewId = basIncrAlpha(Me.txtMyOldId)
[tab][tab][tab]'Me.txtMyOldId = "MARD0001" so
[tab][tab][tab]'So MyNewId = "MARD0002"
and may easily be then assigned to some bound field ...


End Sub

Of course, this is highly speculative!

MichaelRed
m.red@att.net

Searching for employment in all the wrong places