Alphanumeric Auto Number like @###

[bussb]

Question: How can I program Access 2000 to sequentially create the
scheme for each new record:
A001, A002, A003, ...A999, B001, B002, ....B999, C001, C002 and so on,
ending at Z999.

I am linking a database of tools in our factory to a database, and
each tool will have its unique barcode. Each tool has a unique
identifier "ToolNum" which normally I would just make the AutoNumber,
but we have thousands of tools. If I have a barcode with 4 numeric
digits, that allows me 9999 unique labels. However, if I use the
scheme of one letter and three numbers, A001, A002,...A999, B001,
B002, etc. I can get a total of 26,000 entries before I have to add a
fifth digit to the barcode. The less digits in the barcode, the
smaller the size of the barcode, the smaller the label.


Thank you!

 

[John W. Vinson]

Question: How can I program Access 2000 to sequentially create the
scheme for each new record:
A001, A002, A003, ...A999, B001, B002, ....B999, C001, C002 and so on,
ending at Z999.

I presume you'll be entering data for these tools on a Form (if you're
planning to use a table datasheet... don't; it's not designed for the
purpose).

You can put code in the Form's BeforeInsert event like the following:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim intLet As Integer
Dim strIDNext As String
strID = NZ(DMax("[ID]", "[YourTable]"), "A000")
If Mid(strID, 2) = 999 Then
' need to increment the letter; use Asc() to extract it
intLet = Asc(strID)
If intLet = 90 Then ' already up to Z999
MsgBox "Turn off the computer; go home. You're out of numbers.", vbOKOnly
Else
strIDNext = Chr(intLet + 1) & "000"
End If
Else
strIDNext = Left(strID, 1) & Format(Val(Mid(strID, 2))+1), "000")
End If
End Sub

Untested air code... don't hesitate to post back if there are problems!

John W. Vinson [MVP]