Auto Increment by 1 Letter
2007. I just can't get the module into my form...Confused as all h**l right
now...
"Dorian" wrote:
What version of Access are you using? I use Access 2003, I do not have
ability to use the later versions. There should be an option like Tables,
Forms, Queries, Reports and Modules. You need to create a new module and
place the function in there. You could also put the module in the form that
will use it. You have to go into Forms, select your form, choose Design and
then View--Code.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
"Cinquefoil22" wrote:
I am so sorry to pick at your brain here...
I went to visual basic, selected the module icon and copied your formula
into it and saved it as Trace Code...
Now what do i do?
LOL, i am so confused right now and truly appreciate your help...
"Dorian" wrote:
Here is function:
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
Put this in your modules then you can call it from your form.
You pass this function the existing 3-letter code and it returns the next
one in sequence. Once it reaches ZZZ it goes back to AAA.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
"Cinquefoil22" wrote:
ok, but where am i typing the string at? I think, honestly, I am just
confused all the way around on what you are telling me to do....
Sorry...
"Dorian" wrote:
Its pretty easy to do. Set up a string with all letters in sequence with an
extra A at end.
Dim strAB = "A........ZA"
Then you use INSTR function (look in HELP for details) to locate the letter
and then just add 1 to position to the next one.
You will have to check when last letter goes from Z to A since that will
trigger the same logic for the second letter. Same for the second letter
changing from Z to A triggers changing the first letter.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
"Cinquefoil22" wrote:
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....
Do you think you can help me figure out how to do this?
|