Auto increment letters in MS Access

G

Guest

Joined
Jun 25, 2005
Messages
73
Reaction score
0
How do I automatically make record #2 have a value of B,
given that record 1 has a value of A?

Record three should automatically populate with C, etc.

Any ideas?
 
How do I automatically make record #2 have a value of B,
given that record 1 has a value of A?

Record three should automatically populate with C, etc.

Any ideas?
Click to expand...
What does record #27 have? Record 3456?

I have a vague idea that if you manually assign the first letter this could
be one of those rare cases where recursion is actually of some use.
 
As Mike indicates elsewhere in this thread, this will limit the table to a
maximum of 26 records. That said, here's a simple example using DMax(),
which will work well enough in a single-user app. In a multi-user app,
you'll need to give consideration to other issues, like handling record
locks when two users try to add a new record at the same time.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strChar As String
Dim intAsc As Integer

If Me.NewRecord Then

'@ is the character before A in ASCII order.
strChar = Nz(DMax("TheLetter", "tblTest"), "@")
intAsc = Asc(strChar)
If intAsc < Asc("Z") Then
intAsc = intAsc + 1
Me!txtTheLetter = Chr$(intAsc)
Else
MsgBox "Sorry, you've used all 26 letters."
Cancel = True
End If
End If

End Sub
 
Back
Top