From: Cinquefoil22 on
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
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
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
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]