Filter form based on subform 1

[DSGF]

Hi All,
I am trying to open a form with a subform and filter the main form based on whats been assigned in it's sub-form.
I have four tables

Employees Region Channel Responsibilities

EmployeeID RegionID ChannelID ResponsID
LN Region Channel EmployeeID
FN RegionID
Addr1 ChannelID
Addr2
etc.

form![AAA_Employee_Manage] gets it data from Employees and displays all it's data. It's subform, [AAA_Responsinilities] gets it's data from Responsibilities and is linked by EmployeeID.

On a seperate Menu form I have a Command6 and a Combo7.
Combo7 code is:

Private Sub Combo7_Change()
Command6.Caption = "add/delete/edit from " & Me.Combo7.Column(1)
Command6.Tag = Me.Combo7.Column(1)
End Sub

...which simply changes Combo6's Caption and sets it's Tag to the Combo7 displayed value. ie. "South Central"

The Command6 code is:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
        
    If Me.Command6.Tag <> "" Then
    stLinkCriteria = "[RegionID] = " & Combo7.Value
    Forms!AAA_Employee_Manage.Label43.Caption = Me.Command6.Tag

    End If
   
    stDocName = "AAA_Employee_Manage"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    If Me.Command6.Tag = "" Then
    Forms!AAA_Employee_Manage.Label43.Caption = "All Regions"
    End If

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
    
End Sub

So stLinkCriteria = "[RegionID] = " & Combo7.Value (which could be.."[RegionID] = 3" does not work because there is no RegionID in my Employees table.

I think the stLinkCriteria needs to be assigned somehow to show me only the Employees that have had Region (?) (and what ever Channel) assigned to them in the subform. An Employee could have several Regions and Channels assigned to them and would need to be included if any assigned Region matched the region that was selected from Combo7 on the Menu. Additionally, all assigned Regions and Channels should remain visable in the Responsibilities subform.

I hope I have explained this well enough. Please let me know if I can clarify anything.
Any help or suggestions will be much appriciated.
Thanks in advance.

DSGF

 

[PHV]

You may try this:
stLinkCriteria = "EmployeeID In (SELECT EmployeeID FROM Responsibilities WHERE RegionID=" & Me!Combo7.Value & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DSGF]

Thanks PHV.
I will try this and respond shortly.
What is that "In" called. To better understand why this works, do you have a suggested keyword or topic that I could search on?

 

[DSGF]

OK, Here's what happended.

I replaced:
stLinkCriteria = "[RegionID] = " & Combo7.Value
with..
stLinkCriteria = "EmployeeID In (SELECT EmployeeID FROM AAA_Responsinilities WHERE RegionID=" & Me!Combo7.Value & ")"

When I press Command6 the form does open but shows me all 100 employees instead of the 14 that actually have the Central (second in Combo7) assigned to them.

When I look at the form's filter it now says:

Lutron_EmployeeID In (SELECT Lutron_EmployeeID FROM AAA_Responsinilities WHERE RegionID=2)

Did I miss something? Any thoughts?

 

[MajP]

http://www.1keydata.com/sql/sqlin.html

 

[DSGF]

Ok. Thank you MajP that link helps me understant In vs WHERE.
Do you know...
Does it make any difference that the two tables are completely seperate and are only related by the form EmployeeID and the subform EmployeeID?

 

[DSGF]

PHV,
You were absolutely correct.
The answer was:
stLinkCriteria = "EmployeeID In (SELECT PersonID FROM Responsibilities WHERE RegionID=" & Me!Combo7.Value & ")"
My rookie mistake was using EmployeeID in one table and PersonID in the other table.
Thanks again for your help!