I'm not entirely clear on exactly what your scheme is but I wrote a function recently that should help you.
This is a generic function that takes an input string and increments the RIGHT MOST group of alphanumeric characters by one character. An alphanumeric character being A through Z, a through z, and 0 through 9. I wrote this function to function similiar to the String.succ or String.next method in the ruby scripting language.
Here's ruby's description:
Returns the successor to
str. The successor is calculated by incrementing characters starting from the rightmost alphanumeric (or the rightmost character if there are no alphanumerics) in the string. Incrementing a digit always results in another digit, and incrementing a letter results in another letter of the same case... If the increment generates a “carry,'' the character to the left of it is incremented. This process repeats until there is no carry, adding an additional character if necessary.
Code:
"abcd".succ #=> "abce"
"THX1138".succ #=> "THX1139"
"<<koala>>".succ #=> "<<koalb>>"
"1999zzz".succ #=> "2000aaa"
"ZZZ9999".succ #=> "AAAA0000"
"***".succ #=> "**+"
https://ruby-doc.org/core-2.4.2/String.html#method-i-succ
Here's my vba port:
Code:
'succ increments an alphanumeric string, returns the (succ)essor to str
' this is my implementation of ruby's String.succ / String.next method
' it will only increment the right most group of alphanumeric characters
' it will add one character if necessary eg 2 char input "ZZ" becomes 3 chars "AAA"
' or "99" becomes "100".
' Numbers always increment to the next number, letters to the next letter.
' https://ruby-doc.org/core-2.3.1/String.html#method-i-next
Public Function succ(ByVal str) As Variant
On Error GoTo ErrHandler
Dim str_len As Integer 'input string's length
Dim i As Integer 'index as we loop through input string
Dim b As Byte 'ascii byte value of character we're evaluating
Dim b_prev As Byte 'ascii byte of last alphanumeric character evaluated
Dim carry As Boolean 'does the loop need to keep going?
str = " " & str 'insert space to trick for loop to behaving correctly when we have carry over after index = 1 and avoid extra error handling
str_len = Len(str)
b_prev = False
carry = True
For i = str_len To 1 Step -1 'Loop through the string backwards (right to left), i = current index of loop
b = Asc(Mid(str, i, 1)) 'b = the ascii value of the character at string index i
Select Case b
Case 65 To 90, 97 To 122, 48 To 57 'case if A to Z, a to z, or 0 to 1 (alphanumerics)
b = b + 1 'we found a alphanumeric so increment it
'If the increment needs to roll back then handle it. eg Z increments back to A
' if there is a roll back (or carry over) then we will need to continue the for loop to the next char
Select Case b
Case 91
b = 65 '=A
Case 123
b = 97 '=a
Case 58
b = 48 '=0 (zero)
Case Else
'There was no carry over, we're already almost done
carry = False
End Select
'replace the character at index i with the incremented value
Mid(str, i, 1) = Chr(b)
'if there was no carry over then we're done, exit loop. otherwise repeat process until no more carry overs
If Not carry Then Exit For Else b_prev = b
Case Else
'either we havn't hit an alphanumeric yet in our right to left scan
' - OR - we need to add an additional character. Eg given input string "9" increments and rolls back to "0" BUT
' we need to insert another character in this case so result equals "10", not just "0". Insert that char into
' the string here.
If b_prev Then 'we need to insert a new character
Select Case b_prev
Case 65 'A
b = 65
Case 97 'a
b = 97
Case 48 '0 (zero)
b = 49
End Select
str = Left(str, i) & Chr(b) & Right(str, str_len - i)
Exit For ' We are done
End If
End Select
Next i
'return results without the extra space we inserted at the beginning of the function
' recalcualte len(str) because we could have inserted another character since we started
succ = Right(str, Len(str) - 1)
ExitHandler:
Exit Function
ErrHandler:
debug.print "Error in 'succ' function. Error #" & err.number & ": " & err.description
succ = err
Resume ExitHandler
End Function
So in your case it would increment "AA-01" to "AA-02", "AA-99" to "AA-100", etc. It wouldn't increment characters to the left of the hyphen.
To use it simply copy the function into a VBA module in your project and you might do something like this:
Code:
Dim prev_num as String
Dim next_num as Variant
prev_num = <your code to find what ever the previous number was>
next_num = succ( prev_num )
if not iserror(next_num) then
'Your code here
else
'Your error code here
end if
[EDIT]
Originally Posted by
Squirrel1804
The product when manufactured was done in batches and therefore have batch numbers (eg, AA, AB, AC etc) however when we overhaul the product later down the track we need to give it a specific number to identify it, therefore we mark it up to be AA-01, AA-02 etc.
I wanted to see if I can have the batch number (AA, AB, AC etc) as a combo box so the tester chooses which batch the product was from and then have Access automatically pick the next number in the sequence of that batch.
Okay, maybe forget everything I said. You could just have a numeric field that stores the number portion of the product id number and then concat the batch number and this number field at runtime, correct? What's your table structure? Assuming your product table has a foreign key to a batch table you might make a multi field unique index on this numeric field and the batch foreign key.
For reference here is an example of how you can combine a string batch number and a numeric product number to display the product code
Code:
Sub test()
Dim str As String
Dim num As Byte
Dim code As String
str = "AA"
num = 3
code = str & Format(num, "-00")
MsgBox code
End Sub