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?

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?
>
>

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...-by-1-Letter,2