generate next possible identifier

[BayerMeister]

Hi. I want to code this but it is a bit hard for me as I'm a beginner in VBA:



I have a table filled with some records and a column called... say "X". It is a unique identifier, but is not a key (primary, index etc...). It will just be printed, it is editable.

The structure of X is: A String made of a PREFIX (variable number of chars) and a NUMBER (given number of chars, say 4)

The goal is to write a function "getNextX(prefix)" that will return a new X that will contain the given prefix + the greatest number with this prefix in DB +1 (the next number)

For reasons the max number should not be remembered, but retrieved from DB. So I guess I need a SELECT x FROM table WHERE x LIKE "prefix%", then filter each result for the last 4 letters (the rest after the prefix), find the greatest number, increment +1, making it a string least 4 letters long (left-padded with zeros), concatenate with prefix and return.

What I haven't done yet is string operations and SQL SELECT statements from code.

Will someone give me guidance, please?
A list of functions needed would perhaps do, though I still fear VBA syntax a bit :-) so I appreciate verbose replies.

[weekend00]

Public Function getNextX(prefix As String, Optional lenofnumber As Long = 4, Optional TableName As String = "yourTable", Optional idcolumnname As String = "x") As String
tstr As String
tstr = Nz(DMax(idcolumnname, TableName, idcolumnname + " like '" + prefix + "*'"), prefix + String(lenofnumber, "0"))
getNextX = prefix + Format(CLng(Right(tstr, lenofnumber)) + 1, String(lenofnumber, "0"))
End Function

[BayerMeister]

Wow, thanks :-)
Was working on it and my way it has 24 lines so far. I'll consider using your elegant solution :-)
...but will post my code when tested anyway for newbies to compare :-)

[NoellaG]

Hi,

assuming your prefix has 3 characters, just change the tablaname and fieldname to the actual names:


Public Function getNextValue(strPrefix As String) As Long
getNextValue = DMax("getNumVal([testText])", "tblTest", "left([testText],3) = """ & strPrefix & """") + 1
End Function

Public Function getNumval(strInput As String) As Long
Dim lngOutput As Long
Dim strNumber As String
Dim intC As Integer
If Len(Nz(strInput, "")) = 0 Then lngOutput = 0
For intC = 1 To Len(strInput)
If IsNumeric(Mid(strInput, intC, 1)) Then strNumber = strNumber & Mid(strInput, intC, 1)
Next intC
getNumval = CLng(Nz(strNumber, "0"))
End Function

[BayerMeister]

OK, here is the bullsh** I've written.
Note that values (4) and table names and columns are hardcoded... eh I mean "preconfigured" ;-)

Private Function getNewNoteNumber() As String
    Dim prefix As String, offset As String, off As Integer
    Dim rs As Recordset
    
    prefix = variableGet("prefix")
    offset = variableGet("offset")
    
    Set rs = CurrentDb.OpenRecordset("delivery_notes", dbOpenDynaset)
    Dim num As Integer, max As Integer
    Dim code As String
    
    max = Val(offset)
    
    If Not rs.EOF Then rs.MoveFirst
    Do While Not rs.EOF
        code = Nz(rs!note_number, 0)
        If Len(code) > 4 Then
            If Left(code, Len(code) - 4) = prefix Then
                num = Val(Right(code, 4))
                If num > max Then
                    max = num
                End If
            End If
        End If

        rs.MoveNext
    Loop
    
    max = max + 1
    
    'convert to string
    offset = max
    offset = String$(4 - Len(offset), "0") & offset

    getNewNoteNumber = prefix & offset
    Set rs = Nothing
End Function

It works however. Now I'll consider both of your options.

[BayerMeister]

I'm sorry, there is some garbage in the code forgotten ("off") and features undocumented ("offset") etc.