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, Mar 29 '18 at 19:10
>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, Mar 29 '18 at 19:43
You can see [this answer (first section)](https://stackoverflow.com/a/49509616/7296893). 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, Mar 29 '18 at 19:45
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, Mar 29 '18 at 19:54

score 0 · Answer 1 · answered Mar 29 '18 at 19:26

0

Check out MasterLinkFields/ChildLinkFields.

I guess you could use tabOffices and OfficeID and remove all of this code.

answered Mar 29 '18 at 19:26

Gustav

53,498
7
29
55

Can't use those fields as the forms are not bound to anything. Also, `tabOffices` <> `OfficeID`. `tabOffices` = index of the page, so I'm using the `Select Case` to assign the proper OfficeID. – Scott Mar 29 '18 at 19:28
@Scott Create a table where you assign the tabOffices and OfficeID then use it in the forms query is what Gustav suggest. – ComputerVersteher Mar 29 '18 at 19:42
1: The main form doesn't have to be bound. 2: The subform is set bound to your recordset. Do that initially. – Gustav Mar 29 '18 at 19:46

Erik A · Accepted Answer · 2018-03-29T20:15:03.123

0

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

edited Mar 29 '18 at 20:15

answered Mar 29 '18 at 19:46

Erik A

31,639
12
42
67

Thanks! Got it into the sub and it creates the correct SQL statement when I `debug.print` it. The subform information does not change when I change tabs though. I put a `Me!frmPCLPUpdateSF.Form.Requery` after the `Me!frmPCLPUpdateSF.Form.BuildSQL` statement, but nothing. – Scott Mar 29 '18 at 20:03
Basic debugging: does tabOffices_Change fire correctly (add a debug.print in there)? Add a `Debug.Print Me.Recordsource` at the end of the BuildSQL. Does that change correctly as well? – Erik A Mar 29 '18 at 20:09
`tabOffices_Change` fires correctly. I added the debug at the end of `BuildSQL`. It's cycling through on `Form_Load` six times, then on `tabOffices_Change` four times. Weird. Each time, the SQL statement is correct though, it's just not populating the form. – Scott Mar 29 '18 at 20:18
See the current edit. Avoid setting the recordset on form current, that causes an infinite loop, and access's tricks to break out of it are likely causing problems. – Erik A Mar 29 '18 at 20:20
OK, that fixed the multiple run throughs, but it's still not populating the subform when I change the tab. Again, the SQL statement is correct. – Scott Mar 29 '18 at 20:29
Hm.. I can't reproduce this. Instead of `Me.RecordSource = strSQL` you could try `Set Me.Recordset = CurrentDb.OpenRecordset(strSQL)` – Erik A Mar 29 '18 at 20:36
That gave me an "Invalid use of property" error. Grr.. I have realized that in the time I've spent trying to get this to work, I could have just created seven different forms instead of doing it through programming. But I still want to know why this isn't working. Should be easy! – Scott Mar 29 '18 at 20:54
I'm at a total loss then. Never got an invalid use of property when setting a forms recordset. If frmPHDUpdate is a simple datasheet form, nothing more, then I don't understand why it's behaving this way. – Erik A Mar 29 '18 at 21:01
Thanks for all the help. I can't figure it out... I might have to walk away for a bit.. I'm fearing for my computer! – Scott Mar 29 '18 at 21:02
If you could share a sample section of the database (with as minimal data as possible), I could try debugging it. But maybe tomorrow, been at it too long too. – Erik A Mar 29 '18 at 21:05
One of the subforms is now updating! The properties are exactly the same as the others. Ugh. Thanks for the offer, but I can't really share the data. Awfully sensitive!!! – Scott Mar 29 '18 at 21:11
OMG. I figured it out. Another Select Case under `tabOffices_Change`. Based on the tab, it's `Me!subX.Forms.Requery` where subX=the subform name. Thanks again for all of the help! – Scott Mar 29 '18 at 21:23

Mainform tab control passing value to subform variable for select case

2 Answers2

Linked