Auto generate ID as Alphanumeric

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
I have table with KidsID field,
I want that field to auto increment when new kid is added to the system
I want the format as

Question by:mercybthomas74
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26298079
is the application be use in a multiuser environment?
 if it is, this is not advisable without an Autonumber field.
LVL 20

Expert Comment

ID: 26298110
Why not simply create a bunch of blank records pre-filled in with your values.
When you want to add a kid... grab the first 'blank' record.
This is about the only way you can absolutely control this scenario.
PS.  You could create a single field table with these values already entered... then lookup your real table for the 'last one used' compared to this single field table and apply the number. (probably the same results).

Scott C
LVL 20

Expert Comment

ID: 26298127
In multi environment - as soon as you 'decide and grab' a new record.... enter some pertinent data to it and save it.  That way the 'next guy' won't use it.

Scott C
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 26298133
Yes its going to be  a multiuser environment,
if it is used as Autonumber field, how to use alpha numeric values.... in Autonumber field
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26298288
it will be a different field than the AutoNumber field, that will combine the AutoNumber field value with Aphabet.
how do you determine the Alpha portion of the KidID field?

Author Comment

ID: 26298303
I tried to put the below code as BeforeInsert()
Its not working porprely..

I think some kind of script will enable to generate the field as alphanumeric values, any help would appreciated...
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strID As String
Dim intLet As Integer
Dim strIDNext As String
strID = Nz(DMax("[KidsID]", "[Master Name Table]"), "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 "You're out of numbers.", vbOKOnly
strIDNext = Chr(intLet + 1) & "000"
End If
 strIDNext = Left(strID, 1) & Format(Val(Mid(strID, 2)) + 1, "000")
End If
End Sub

Open in new window

LVL 44

Expert Comment

ID: 26298882
As you can see, alpha and autonumber do not go together in the same field.  If you insist on having them together, then forget the 'auto' part of the number - you are going to have to do it in code.  This is real 'headachy' territory.  I recommend you try to live with just an autonumber and leave the alpha out - it serves no useful purpose.  

Expert Comment

ID: 26301407
Here's a little something I wrote a while back for the same purpose.
Create an Autonumber field on your table AND a  text field.
Make the default value of the text field the result of the function.
Better still, add a Generate ID button to the form...

Pass the value of the Auto number field as the second parameter, and the text you want as the first...
Return value is text to go into your TEXT field. This can be your index...


Public Function IndexGenerator(strPrefix As String, Optional lngIndex As Long) As String
'Used to add a TEXT prefix to The number passed in as lngIndex
'EG: AARN + AutoNumber field creates a unique ID for GRN
'JOB + AutoNumber creates unique Job No etc.

On Error GoTo IG_Err
Dim msg1 As String ' error handling
Dim strNumber As String

strNumber = CStr(lngIndex)
If lngIndex < 10 Then strNumber = "00" & CStr(lngIndex)
If lngIndex < 100 And lngIndex > 9 Then strNumber = "0" & CStr(lngIndex)

IndexGenerator = strPrefix & strNumber


Exit Function


msg1 = Err.Description & vbCr & Err.Number
Select Case Err.Number

Case Else
MsgBox msg1, vbCritical, "Error generating " & strPrefix & " Number"
End Select
Resume IG_Bye

End Function

Open in new window

LVL 20

Accepted Solution

clarkscott earned 2000 total points
ID: 26303120
You can use the autonumber and combine it with the alph characters in another field.
You won't be able to absolutely control the autonumber.  for instance... if a record is created than cancelled before (or after) it's started... that autonumber will never be used again.  You will have 'missing' numbers.

dim s as string
s = trim(str(YourAutoNumberField))
do while len(s) < 3
       s = "0" & s
'--- you now have "001" for autonumber = 1 --
'--- now add this number to your alph stuff --
s = "ABC" & S
YourOtherField = s

Scott C

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question