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

Why are you building the SQL in the Current event? Why don't you just have a sub that builds the SQL that you can trigger manually, and on any event you wish? Why are you even building SQL at all (you can just refer to a control on the main form in the SQL)? Please share some code. — Erik A
>Why are you even building SQL at all (you can just refer to a control on the main form in the SQL)? I'm not really sure what you mean. Can you give an example? If it works, I'd love to try it! — Scott
You can see this answer (first section). It's a good practice, since it avoids many errors with string concatenation. That does require you to move the SELECT CASE to a Switch, but it could certainly work for you. — Erik A
I've provided a more detailed explanation for how to use referring to a control in your specific case in my answer to avoid building and changing the SQL clause. — Erik A

Erik A Erik A · Accepted Answer · 2018-03-29T19:46:37

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
);

Mainform tab control passing value to subform variable for select case

2 Answers