Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

571 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