Auto Increment by 1 Letter
in [Forms]
|
From: Cinquefoil22 on 27 May 2010 19:19 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?
From: Dorian on 27 May 2010 20:05 You need to get the function I gave you into the code for your form. Thats what you should be asking here. How do you get a function into the code for a form in Access 2007? Always state your version when asking a question. -- 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? > >
From: forwardphase on 27 May 2010 20:37 I don't know what code you've gotten before, but here is how I would solve this problem. Step #1 ------- Create module named modActions and paste this function into it: Public Function LetterIncrement(aKey As String) Dim bStr As String, lStr As String, mStr As String, rStr As String bStr = VBA.UCase(aKey) lStr = VBA.Left(bStr, 1) mStr = VBA.Mid(bStr, 2, 1) rStr = VBA.Right(bStr, 1) If (VBA.Asc(rStr) < 90) Then rStr = VBA.Chr(VBA.Asc(rStr) + 1) ElseIf (VBA.Asc(mStr) < 90) Then mStr = VBA.Chr(VBA.Asc(mStr) + 1) ElseIf (VBA.Asc(lStr) < 90) Then lStr = VBA.Chr(VBA.Asc(lStr) + 1) mStr = "A" rStr = "A" End If LetterIncrement = lStr & mStr & rStr End Function Step #2 -------- Create a query (I called mine qryGreatestPhony) that points to your target table. The SQL will read something like this: SELECT TOP 1 tblPhony.ID FROM tblPhony ORDER BY tblPhony.ID DESC; This will give you a single record -- the maximum string identity in your table. Step 3: -------- Create a form with a text field and a button on it. I called the field txtNuevo. Here is the onClick event for this button: Private Sub cmdNew_Click() Me.txtNuevo = modActions.LetterIncrement(DLookup("ID", "qryGreatestPhony")) End Sub If you have questions link to me on Twitter @forwardphase. --- frmsrcurl: http://msgroups.net/microsoft.public.access.formscoding/Auto-Increment-by-1-Letter,2
From: Marshall Barton on 27 May 2010 22:58 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? > Instead of posting the same question over and over, you should post followup questions to your original thread. I tried to answer this in one of your other posts so now you'll have to find all those posts and see what replies you have received. -- Marsh MVP [MS Access]
|
Pages: 1 Prev: Auto Increment by 1 letter Next: Export Table Records to txt file |