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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|