Search code examples
formsms-accessauto-generate

Maximum value based on initials (MS access forms)

I am trying to create a form where the ID will give the maximum number based on the initials. Below is a sample of the records in the table:

Employee ID |   Employee Name   |   Designation 
------------+-------------------+--------------
CL-100      |   Akram waziullah |   Cleaner
GU-100      |   Shopno Lal      |   Guard       
LG-100      |   Karim Lalon     |   L&G Serviceman  
GU-101      |   Lal Miah        |   Guard

I want to create a form where the system can automatically generate an ID based on the initials. For example if i select guard as the designation, the ID will be GU-102 and if i select cleaner, it will be CL-101 and if i select Electrician, it will be EL-100 (Since no records previously exists of electricians). I am able to generate the first two letters of Designation but i do not know how i can extract the maximum value based on the initials. Need help...

Solution

  • In the relevant control in the form you can use the below code.

    DMAX("CLng(Right([Employee ID],(LEN([Employee ID])-3))","[tblEmployees]","[Designation]='" & Me.Designation & "'")+1

    Alternatively you can add it to the before Update event of the Form

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    me.EmployeeID = DMAX("CLng(Right([Employee ID],(LEN([Employee ID])-3))","[tblEmployees]","[Designation]='" & Me.Designation & "'")+1
End Sub