Increment alphabets automatically

  • Thread starter Thread starter SMatthew
  • Start date Start date
S

SMatthew

Could some body Help me by letting me know how to
increment alphabets (A,B,C....) automatically in
MSAccess 2002.

Thanks in advance
 
Dim iLetter As Integer

For iLetter = 65 to 90
' Output Capital Letters
Debug.Print Chr(iLetter)
Next

For iLetter = 97 to 122
' Output SmallLetters
Debug.Print Chr(iLetter)
Next
 
SMatthew said:
Could some body Help me by letting me know how to
increment alphabets (A,B,C....) automatically in
MSAccess 2002.
Click to expand...

Danny's code will increment alphabets as you asked.

Here is some code to generate a true alphanumeric with a range of AA000 to
ZZ999, it will error out after this, but you can write code to cancel if it
does (the highest value being 576,000 records) Alter this code for A00001 to
Z99999, the highest value will be for 2,599,974 records.

Function AlphaNumGenerate () As String
' © Arvin Meyer 03/12/1996
' Permission to use or alter this code is granted
' as long as the copyright notice remains intact

' Create a table, called 'tblCounter with one field "Value", Long
Integer data type.
' Use this field as a counter.
' The only record in the field initializes the counter (I used 0 for
AA000)
' In DefaultValue property use: =AlphaNumGenerate Or use with form as
below

Dim db As Database
Dim rst As Recordset
Dim lngCntr As Long
Dim intRetry As Integer
Dim intNum As Integer, intA As Integer, intB As Integer
Dim strANum As String
On Error GoTo ErrorAlphaNumGenerate
Set db = CurrentDB()
Set rst = db.OpenRecordset("tblCounter", db_Open_Dynaset) 'Open table
with the counter
rst.MoveFirst
rst.Edit
rst!Value = rst!Value + 1
rst.Update
lngCntr = CLng(rst!Value) - 1
intNum = lngCntr Mod 1000
intA = (lngCntr \ 1000) Mod 26
intB = (lngCntr \ 1000) \ 26
'Generate the AlphaNumber
strANum = Chr$(intB + 65) & Chr$(intA + 65) & Format$(intNum, "000")
AlphaNumGenerate = strANum
ExitAlphaNumGenerate:
Exit Function
ErrorAlphaNumGenerate: 'If someone is editing this record trap the error
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "Another user editing this number"
Resume ExitAlphaNumGenerate
End If
Else 'Handle other errors
MsgBox Str$(Err) & " " & Error$, 48, "Problem Generating Number"
Resume ExitAlphaNumGenerate
End If
End Function
---------------------------------
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top