A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Auto Increment by 1 Letter



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 10:01 PM posted to microsoft.public.access.tablesdbdesign
Cinquefoil22
external usenet poster
 
Posts: 3
Default Auto Increment by 1 Letter

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?

Ads
  #2  
Old May 27th, 2010, 10:03 PM posted to microsoft.public.access.tablesdbdesign
Cinquefoil22
external usenet poster
 
Posts: 3
Default 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?

  #3  
Old May 27th, 2010, 10:04 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default Auto Increment by 1 Letter

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?

  #4  
Old May 27th, 2010, 10:51 PM posted to microsoft.public.access.tablesdbdesign
Cinquefoil22
external usenet poster
 
Posts: 3
Default Auto Increment by 1 Letter

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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 OfficeFrustration.
The comments are property of their posters.