Maximum value based on initials (MS access forms) -

I am trying to create a form where the ID will first be given on the basis of initial. Below is a sample record of the table:

  employee ID. Employee name. Designation ------------ + ------------------- + -------------- CL- 100 | Akram Wazilullah | Cleaner GU-100 Shopo Lal | Guard LG-100 | Karim Lalan | L & G Soldier GU-101 | Red Mia | Guard  

I want to create a form where the system can automatically generate the first letter based ID. For example, if I select the guard as the designation, then the ID will be GU-102 and if I select the cleaner it will be CL-01 and if I select the electrician, then it is EL-100 (Because there is no record before the electricians). I am able to generate the first two letters of the designation, but I do not know how to get the maximum value based on the first letter. Need help ...

In this form you can use the code below in the relevant control. Alternatively you can call it on the form of " Can be added before the update event of

  private sub form_brain predetermined (cancellation as integer) me.EmployeeID = DMAX ("CLNG (right ([employee ID], (LAAN ([employee] ID]) - 3)), "[TBEMP staff]", "[designation] = '& amp; Me.Designation +1 and sub