C
Cinquefoil22
Simple question, hope I can get an answer....
Ok, I have created a table and form for our company to keep track of incoming
inventory. Being that we work with metals, each piece that comes in is
assigned
a unique 3 letter value. We started with AAA. What I need to know is once I
enter the item we are receiving is there a way for it to automatically go to
the
next sequence of letters. Example, yesterday we received in. The last
series
of letters I used was BHV. So today, when I receive in, I want the product
to
automatically be assigned BHW and then BHX and so on. After I use BHZ my
next sequence would be BIA. Ultimately when I get to BZZ, my next sequence
would be CAA and so on....
Now, here is the thing, I am not code saavy, so if someone could explain to
me how exactly I would set this up and where I would put it and how I would
get it in my form, I would appriate it....
Do you think you can help me figure out how to do this?
Many Thanks!!!
P.S. Someone did try to help me and this was the code they gave me:
Public Function Encode(strCode As String) As String
Dim strAB As String, c1 As String, c2 As String, c3 As String
Dim p As Integer
strAB = "ABCDEFGHIJKLMNOPQRSTUVWXYZA"
c1 = Left$(strCode, 1) 'first letter
c2 = Mid$(strCode, 2, 1) 'middle letter
c3 = Right$(strCode, 1) 'right letter
' Increment last letter
p = InStr(strAB, c3)
c3 = Mid$(strAB, p + 1, 1)
If c3 = "A" Then
' Increment middle letter
p = InStr(strAB, c2)
c2 = Mid$(strAB, p + 1, 1)
If c2 = "A" Then
' Increment first letter
p = InStr(strAB, c1)
c1 = Mid$(strAB, p + 1, 1)
End If
End If
Encode = c1 & c2 & c3
End Function
Ok, I have created a table and form for our company to keep track of incoming
inventory. Being that we work with metals, each piece that comes in is
assigned
a unique 3 letter value. We started with AAA. What I need to know is once I
enter the item we are receiving is there a way for it to automatically go to
the
next sequence of letters. Example, yesterday we received in. The last
series
of letters I used was BHV. So today, when I receive in, I want the product
to
automatically be assigned BHW and then BHX and so on. After I use BHZ my
next sequence would be BIA. Ultimately when I get to BZZ, my next sequence
would be CAA and so on....
Now, here is the thing, I am not code saavy, so if someone could explain to
me how exactly I would set this up and where I would put it and how I would
get it in my form, I would appriate it....
Do you think you can help me figure out how to do this?
Many Thanks!!!
P.S. Someone did try to help me and this was the code they gave me:
Public Function Encode(strCode As String) As String
Dim strAB As String, c1 As String, c2 As String, c3 As String
Dim p As Integer
strAB = "ABCDEFGHIJKLMNOPQRSTUVWXYZA"
c1 = Left$(strCode, 1) 'first letter
c2 = Mid$(strCode, 2, 1) 'middle letter
c3 = Right$(strCode, 1) 'right letter
' Increment last letter
p = InStr(strAB, c3)
c3 = Mid$(strAB, p + 1, 1)
If c3 = "A" Then
' Increment middle letter
p = InStr(strAB, c2)
c2 = Mid$(strAB, p + 1, 1)
If c2 = "A" Then
' Increment first letter
p = InStr(strAB, c1)
c1 = Mid$(strAB, p + 1, 1)
End If
End If
Encode = c1 & c2 & c3
End Function