Letter Incrimenting ID System

I have a product ID system that works on letters and numbers

LotNumberPrefix
LotNumberId
LotNumberSuffix

If a member puts in a LotNumberPrefix which can be A-Z it must insert the next available LotNumberId which is linked to the prefix. EG.

LotNumberPrefix | LotNumberId | LotNumberSuffix
A | 1
A | 2
A | 3
B | 1
B | 6

If they enter a LotNumberPrefix  and a LotNumberId Manually that exists it must add a LotNumberSuffix automatically Starting with A and incrimenting Like This:

LotNumberPrefix | LotNumberId | LotNumberSuffix
A | 1
A | 2
A | 2 | A
A | 2 | B
A | 2 | C
Nasir Razzaq

Have you started with the code? Would you use three separate textboxes for the three parts?
what is the question exactly?

Increment character ?
in vb ?

you can use function ASC to convert a letter to its ASCII representation (A = 65, B=66,...) that you can easily increment.
The CHR function will do the opposite conversion (CHR(65) = A)

the following code will increment "LotNumberSuffix" from A to B

``````LotNumberSuffix="A"
LotNumberSuffix = Chr(Asc(LotNumberSuffix) + 1)``````
When arrived to Z (ASC(Z)=90) you have to figure out how to stop incrementing

I havent started with the code, and yes three text boxes that will be appended to one another later in the report.
@LazyFolk
>LotNumberSuffix="A"
>LotNumberSuffix = Chr(Asc(LotNumberSuffix) + 1)

What if
LotNumberSuffix="Z"
?

@EasyToHelp
Would suffix change to AA after Z?
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Result As Integer = Asc(TextBox1.Text)
If Result = 90 Then
Result += 7
Else
Result += 1
End If
TextBox2.Text = Chr(Result).ToString
End Sub

The above will change A to Z, when reaches Z changed to a through z.

But the point is, you are trying to have an unqiue ID of a transaction which I think it's not
supposed to be done this way, it's messy and painful.

For me, for your case, I always auto generate the ID. I used a table of two columns, Prefix and RunningNumber column to Keep the data. Every time a new ID is generated, I update the RunningNumber column.

Or at least, when a user attempts to insert an existing number, you try to get the max value of that number and advise the user instead of adding suffix.
as I said in the post :

When arrived to Z (ASC(Z)=90) you have to figure out how to stop incrementing

No rule has been defined for the case where suffix is bigger than "Z".
Therefore I cannot help implementing it

You can
- continue with lower case
- Forbid increment

After Z it must go to AA and then go to BB then CC until ZZ then it will goto AAA
sorry for the delay, I was travelling.
Here is a recursive function that achieve the following count :

A -> ... -> Z -> AA -> ... -> AZ -> BA -> ... -> BZ -> ... -> ZZ -> AAA -> ...

``````Function getNextSuffix(actualSuffix) As String
Dim suffix As String

suffix = actualSuffix

If Right(actualSuffix, 1) = "Z" Then
If Len(actualSuffix) = 1 Then
suffix = "AA"
Else
suffix = getNextSuffix(Left(actualSuffix, Len(actualSuffix) - 1)) + "A"
End If
Else
suffix = Left(actualSuffix, Len(actualSuffix) - 1) + Chr(Asc(Right(actualSuffix, 1)) + 1)
End If
getNextSuffix = suffix
End Function``````

you can easily test this function with the sample code below (copy paste sub and function in excel vba and run it)

``````Sub test()
Dim mySuffix As String
Dim nextSuffix As String
mySuffix = "A"

For i = 1 To 28
For j = 1 To 26
Cells(i, j).Value = mySuffix
mySuffix = getNextSuffix(mySuffix)
Next
Next
End Sub``````

The attached screenshot show the result on my computer.