Auto Increment by 1 Letter

[Cinquefoil22]

Ok. I work in a welding shop and we are in the process of creating a database that allows us to keep track of our inventory coming in and going out.
Each piece of new material we bring in is assigned a Trace Code which is simply three letter. First piece brought in is AAA. Next piece AAB and so on...When you get to AAZ the next one becomes ABA. Ultimately when you get to AZZ your next series is now BAA. Is there a way for me to set up the form to automatically do that?


I have already set up the form and table and they are named Inventory Receiving List. The field for this data is named Trace Code. Any help anyone could offer would be greatly appreciated.
Please be mindful in answering that I really am not very strong with formulas and modules.
Many Many Thanks in advance!!

[Datagopherdan]

An easy way to do this would be to have a cross-reference table where you have 2 fields, ID and LetterID Field. Your ID field would be numeric and your letter id field would be in the format that you mentiioned AAA, AAB etc. You would then have an Auto-ID field in your main table that would perform a lookup on the ID in that table. For example, if the Auto-ID field was 2 then, it would pull AAB. This probably is not the best way to set it up but it's quick and easy.

Ok. I work in a welding shop and we are in the process of creating a database that allows us to keep track of our inventory coming in and going out.
Each piece of new material we bring in is assigned a Trace Code which is simply three letter. First piece brought in is AAA. Next piece AAB and so on...When you get to AAZ the next one becomes ABA. Ultimately when you get to AZZ your next series is now BAA. Is there a way for me to set up the form to automatically do that?
I have already set up the form and table and they are named Inventory Receiving List. The field for this data is named Trace Code. Any help anyone could offer would be greatly appreciated.
Please be mindful in answering that I really am not very strong with formulas and modules.
Many Many Thanks in advance!!

[TheShabz]

manually that would take forever manually. 26^3 combinations.

Hopefully someone strong in VBA can help put this into working code but heres the logic of what I would do
A = 1
B = 2
...
Z = 26

dim 3 ints, hun (hundreds place), ten (tens place) one (ones place)

hun = 1, ten = 1, one = 1

fill a recordset with the following logic
do while one < 27
current record = hun & ten & one
one = one +1
loop
then once that's done, increment ten +1 and loop again, once ten = 27, increment hun +1 and keep going.

Logic is since you cant count by letter, you count by number and assign a letter to the number before entering into the table.

If anyone can mock up this in VBA, I think it would be great to store in the code repository.

[NoellaG]

Hi,
try

Public Function GetNextChar(strCode As String) As String

Dim strChars As String
Dim strChar As String
Dim intPos As Integer
Dim strResult As String

strChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
strChar = Right(strCode, 1)
If strChar < "Z" Then
intPos = InStr(1, strChars, strChar)
strChar = Mid(strChars, intPos + 1, 1)
strResult = Left(strCode, 2) & strChar
Else
strChar = Mid(strCode, 2, 1)
If strChar < "Z" Then
intPos = InStr(1, strChars, strChar)
strChar = Mid(strChars, intPos + 1, 1)
strResult = Left(strCode, 1) & strChar & "A"
Else
strChar = Left(strCode, 1)
If strChar < "Z" Then
intPos = InStr(1, strChars, strChar)
strChar = Mid(strChars, intPos + 1, 1)
strResult = strChar & "AA"
Else
strResult = "###" 'last code is used
End If
End If
End If
GetNextChar = strResult


End Function

[TheShabz]

yea, do what Noella mentioned. It's much cleaner than my brute force C/C++ logic