Autonumber Using alphanumeric (text and number) data type

[mose Mbugua]

How can i generate the next available record number to appear in the primary key field on a data entry form?

For example, i want to add (admit) a new student but the primary key "Admission Number" has both text and numbers (C001).

When i open the Admission form i wish it to look at the last admission number record, add 1 to it, then prepopulate the id field

[jforbes]

This can get a little tricky, timing wise, if multiple people are creating records. So if you are running where multiple people are pulling a Key, you will want to pull the key and save the record right away, or wait to create the key on save.

Either way it's basically the same. Some people like to use a Key Table but most of the time it's easiest to just look up the next value with a function. Here is one:

Public Function getKey(ByRef sTable As String, ByRef sField As String, ByRef iKeyLen As Integer, ByRef sPrefix As String) As String
    Dim sLastKey As String
    sLastKey = DMax(sField, sTable)
    getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
End Function
 

You can use this type of function as the DefaultValue for a Control on a Form or have it called from VBA if you are doing some magic to make sure all your many users are getting unique values.

[mose Mbugua]

i would like it to be called from vba but i don't know how to do it. am a newbie to code. which field on the control's property sheet should i type the code?

[jforbes]

The easiest way is to put the formula in the DefaultValue Property for your AdmissionNumber.