Creating a Unique Employee ID

[Liam Manning]

Hi all,

I am trying to create a unique Employee ID, but I am having a few issues.

The ID must contain 7 characters in the following Format.

* Characters 1-3 = "LTS"
* Characters 4-5 = Starts at "AA", then when 6-7 reaches 99 changes to "AB" and so on.
* Characters 6-7 = Starts at "00" or "10" (Doesn't matter), and rolls through to "99" before reseting to it's start point, as well as change characters 4-5 to the next in the sequence.


We already use this method but on paper and a looking to move to an electronic system, hence the question.

Many thanks,
LSM1604

[patjones]

This is an issue that comes up from time to time on the forum. You seem to recognize the need for uniqueness, and have a method for achieving that (presuming that you have few enough employees that this method won't exhaust ID's). So my question for you is: what is your question? Are you simply looking for a way to assign them?

Pat

[Liam Manning]

Hey,

Thank you for taking the time to reply.

All employees are currently maintained in a paper based system hence, already having the unique ID format.

However, we are looking to move this forward and use an electronic system. It would be benefical for us to have an automated number generator in the format of the existing system to allow a smooth transition.

I require a method for achieving this. (Unique ID generation and implementation)

Many thanks,
LSM1604

[NeoPa]

You would want a form that is used for entering such employees. A mistake often made is to generate the ID before the record is saved. You should avoid this as it allows for duplicate IDs to be generated and noted before the save fails (potentially losing other work). For this reason I suggest the code is contained within the Form_BeforeUpdate() event procedure. The Default Value of the control could be set to "TBA" to make it clear to the operator what's going on.

The code needs to handle finding the maximum value so far used; applying your logic for working out the next available value; importantly - checking that the record being saved is a new one; etc.

Assumptions:-

1. The table name is [tblEmployee].
2. The field name in the record for Employee ID is [EmployeeID].
3. The control name for the Employee ID is [txtEmploeeID].
4. Where you say :
   * Characters 6-7 = Starts at "00" or "10" (Doesn't matter),
   You really mean :
   * Characters 6-7 = Starts at "00" or "01" (Doesn't matter),
   I will follow the "00" option anyway.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim intNo As Integer
    Dim strID As String, strX As String
 
    With Me
        If Not .NewRecord Then Exit Sub
        strID = Nz(DMax("[EmployeeID]", "[tblEmployee]"), "")
        If strID = "" Then
            .txtEmployeeID = "LTSAA00"
            Exit Sub
        End If
        intID = Int(Right(strID, 2)) + 1
        If intID > 99 Then
            intID = 0
            strX = Mid(strID, 5, 1)
            strX = IIf(strX = "Z", "A", Chr(Asc(strX) + 1))
            Mid(strID, 5, 1) = strX
            If strX = "A" Then Mid(strID, 4, 1) = Chr(Asc(Mid(strID, 4)) + 1)
        End If
        Mid(strID, 6, 2) = Format(intID, "00")
        .txtEmployeeID = strID
    End With
End Sub

There is no warning in this code when the range is exceeded. That can be added, and probably should be, but it's not a part of this question.