Auto increment letters in MS Access

[Brian Mingus]

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?

 

[Mike Painter]

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?

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.

 

[Brendan Reynolds \(MVP\)]

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