Increment value based on selected category

[qwerty70]

My mainform frmEmployee is linked to a tblEmployee, and have 2 controls:

1) EmpNum (textbox)
2) Category (combo box). List values are 'Employee' and 'Guest'.

Within the frmEmployee, there are also 2 hidden subforms (fsubEmp & fsubGU) each connected in their own source query. Each query
has this sql code:

 SELECT Max(tblEmployee.EmpNum) AS MaxOfID FROM tblEmployee;

What I would like to happen here is whenever I choose "Employee" from the Category, the EmpNum field should give me an autonum that
read as "EMP-1", and "EMP-2" for the second record..so forth and so on. While if I choose "Guest", then the EmpNum should autonum as GU-1...etc..etc. I would like the EmpNum to increment based on the selection in the Category.

Here's my code so far:

Private Sub Category_BeforeUpdate(Cancel As Integer)

       Dim strID As String
       Dim lngID As Long
       
    If Me.Category.Value = "Employee" Then
       Me![fsubEmp].Form.Requery
       lngID = Mid(Me![fsubEmp].Form![MaxOfID], 4) + 1
       Debug.Print "EmpNum: " & lngID
       strID = "EM-" & CStr(lngID)
       Me![EmpNum] = strID
       Me![EmpNum].Requery
       
    ElseIf Me.Category.Value = "Guest" Then
       Me![fsubGU].Form.Requery
       lngID = Mid(Me![fsubGU].Form![MaxOfID], 4) + 1
       Debug.Print "EmpNum: " & lngID
       strID = "GU-" & CStr(lngID)
       Me![EmpNum] = strID
       Me![EmpNum].Requery
    End If

End Sub

Above code works well if I constantly use "Employee" as the category. The EmpNum is incrementing without any problem. i.e, EM-1, EM-2, EM-3, etc. But if I select "Guest" for my fourth record, the EmpNum becomes "GU-4", which should be "GU-1" because it is my first record for Guest category.

Please help the novice. Any help or suggestion is highly appreciated.

Regard,

qwerty70

 

[KenReay]

Hi

If the sql to which each subform is bound is the same, ie SELECT Max(tblEmployee.EmpNum) AS MaxOfID FROM tblEmployee;

then surely you would expect to get the result you are getting?

You need a guest table and SQL of

SELECT Max(tblGuest.EmpNum) AS MaxOfID FROM tblGuest;

for sub form for guest (fsubGU ?)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[qwerty70]

KenReay,

Thanks for the prompt reply.

Is there any other way beside from creating another table?

If no, then I would try your suggestion and let you know.

Thanks,

qwerty70