Mainform tab control passing value to subform variable for select case

Question

I've been searching this for the last three hours, so I'm sorry if this has been asked and answered, but I can't find the solution.

I have a mainform, frmPHDLP and subform (continuous datasheet) frmPHDUpdate. Neither are bound forms. frmPHDLP has a tab control on it which displays real world office locations. The subform will display a list of employees at each location based on the tab selected. I am setting the RecordSource in the subform in VBA.

The first tab works perfect. The problem is, when I choose a new tab on the mainform, I cannot get the subform to requery the SQL with the new location. The SQL statement is built in the Form_Current event of the subform.

Apparently running Me!frmPHDUpdate.Form.Requery does not fire the Form_Current event on the subform. Because why would it?

THREE HOURS. Nada. Thanks for any help.

frmPHDLP Code:

Private Sub tabOffices_Change()
    Me!frmPCLPUpdateSF.Requery

End Sub

frmPHDUpdate Code:

Private Sub Form_Current()
    Dim strSearch As String
    Dim strSQL As String

    Select Case Me.Parent!tabOffices.Value
        Case 0
            strSearch = "8401"
        Case 1
            strSearch = "8400"
        Case 2
            strSearch = "8403"
        Case 3
            strSearch = "8402"
        Case 4
            strSearch = "8404"
        Case 5
            strSearch = "8405"
        Case 6
            strSearch = "8413"
        Case 7
            strSearch = "8411"
    End Select

    strSQL = "SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID FROM tblOffices INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office WHERE (((tblOffices.OfficeID)= " & strSearch & "));"
    Me.RecordSource = strSQL

End Sub

Answer 1

You should make your code more modular. Also, you should not change the forms recordsource on Form_Current, since that requeries the form, and triggers another current, and that triggers an infinite loop. This might all be caused by Access avoiding that infinite loop.

frmPHDUpdate Code:

Public Sub BuildSQL()
    Dim strSearch As String
    Dim strSQL As String
    Select Case Me.Parent!tabOffices.Value
        Case 0
            strSearch = "8401"
        Case 1
            strSearch = "8400"
        Case 2
            strSearch = "8403"
        Case 3
            strSearch = "8402"
        Case 4
            strSearch = "8404"
        Case 5
            strSearch = "8405"
        Case 6
            strSearch = "8413"
        Case 7
            strSearch = "8411"
    End Select
    strSQL = "SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID FROM tblOffices INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office WHERE (((tblOffices.OfficeID)= " & strSearch & "));"
    Me.RecordSource = strSQL
End Sub

Private Sub Form_Load()
    Me.BuildSQL
End Sub

frmPHDLP Code:

Private Sub tabOffices_Change()
    Me!frmPCLPUpdateSF.Form.BuildSQL
End Sub

Or, even better: move your case statement to the subforms SQL:

As the record source for frmPHDUpdate

SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID 
FROM tblOffices 
INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office 
WHERE tblOffices.OfficeID = Switch(
    Forms!frmPHDLP!tabOffices = 0, 8401,
    Forms!frmPHDLP!tabOffices = 1, 8400,
    Forms!frmPHDLP!tabOffices = 2, 8403,
    Forms!frmPHDLP!tabOffices = 3, 8402,
    Forms!frmPHDLP!tabOffices = 4, 8404,
    Forms!frmPHDLP!tabOffices = 5, 8405,
    Forms!frmPHDLP!tabOffices = 6, 8413,
    Forms!frmPHDLP!tabOffices = 7, 8411
);