Using the results from one query as the filter for another.

[SteelFire via AccessMonster.com]

I have a form that I am using as a "Pick the filter for a query". It has 9
combo boxes that help to filter the query, but only one is giving me a
problem. The other 8 are filtering off of one table. The last one needs to be
set up so that it will work as so:
The user enters the value that he wants to search by, but the information
that he is entering is found on "tblCustomers". "tblCustomers" has four
fields, CustomerID (PK), Company, Location, Park. We are looking by Location.
I have another query, "qryCustomers", that will filter out all of the
Customers by Location. The only bad thing is that some Customers are at the
same location. This means that I sometimes get back more then one record from
this search. The thing that I need from "qryCustomers" is the results from
the CustomerID field that gets listed. The CustomerID field in "tblCustomers"
relates to CustomerID in "tblPartTable". "tblPartTable" is the table where
all of the other combo boxes reference to. I don't know how to find all of
the Parts in "tblPartTable" with the CustomerID of # when I am entering in
the Location from tblCustomers.

This is the format that I am using with the other 8 combo boxes:
I "Dim strWhere As String" and use that string as the WHERE for the query.
For each combo box I have:
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "([Category] = """ & Me.cboCategory & """) AND
"
End If
It then adds all of the combo boxes together and filters with it. I could use
some help with setting up the IF command that would do what I need.
Any help would be nice and very... helpful.

 

[ErezM via AccessMonster.com]

hi
in your Where-clause building proccess, write something like

"...And CustomerID In(Select CustomerID From tblCustomers Where LocationID="
& Me.cboLocation & ")"

hope it helps you
Erez

I have a form that I am using as a "Pick the filter for a query". It has 9
combo boxes that help to filter the query, but only one is giving me a
problem. The other 8 are filtering off of one table. The last one needs to be
set up so that it will work as so:
The user enters the value that he wants to search by, but the information
that he is entering is found on "tblCustomers". "tblCustomers" has four
fields, CustomerID (PK), Company, Location, Park. We are looking by Location.
I have another query, "qryCustomers", that will filter out all of the
Customers by Location. The only bad thing is that some Customers are at the
same location. This means that I sometimes get back more then one record from
this search. The thing that I need from "qryCustomers" is the results from
the CustomerID field that gets listed. The CustomerID field in "tblCustomers"
relates to CustomerID in "tblPartTable". "tblPartTable" is the table where
all of the other combo boxes reference to. I don't know how to find all of
the Parts in "tblPartTable" with the CustomerID of # when I am entering in
the Location from tblCustomers.

This is the format that I am using with the other 8 combo boxes:
I "Dim strWhere As String" and use that string as the WHERE for the query.
For each combo box I have:
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "([Category] = """ & Me.cboCategory & """) AND
"
End If
It then adds all of the combo boxes together and filters with it. I could use
some help with setting up the IF command that would do what I need.
Any help would be nice and very... helpful.