Firstly you've referred to both CLEC ID and Customer ID columns. I'm
assuming below that the primary key of Customers and the corresponding
foreign keys in both tblEmployeeAssignments and my putative CustomerRegions
table are all called CLEC ID. If not you'll need to make the necessary
amendments to the SQL.
I think I'd be inclined to use a query to filter the main parent form (and
subform if you also wanted that filtered). You'd do this by basing the main
from on a query with parameters which reference the unbound controls on the
main form. With the company and employee controls, which I'll call
txtCompany and cboEmployee for this example, the referenced in the query in
the usual way, testing for OR IS NULL to make it optional; the second would
be referenced by a subquery, but again testing in the outer query for OR IS
NULL to make it optional e.g.
SELECT *
FROM Customers
WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*"
OR Forms!CLECS2MainForm!txtCustomer IS NULL)
AND ([CLEC ID] IN
(SELECT [CLEC ID]
FROM tblemployeeAssignments
WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee)
OR Forms!CLECS2MainForm!cboEmployee IS NULL)
ORDER BY [CLEC Name];
To include the region and/or state you'd add further subqueries on, in the
first instance the table underlying the second subform, which I'll assume is
called CustomerRegions and contains columns CLEC ID and RegionID, and in
the second instance on a query which joins the table underlying the second
subform to the table underlkying its subform, which I'll assume is called
States and contains columns RegionID and StateID:
SELECT *
FROM Customers
WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*"
OR Forms!CLECS2MainForm!txtCustomer IS NULL)
AND ([CLEC ID] IN
(SELECT [CLEC ID]
FROM tblemployeeAssignments
WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee)
OR Forms!CLECS2MainForm!cboEmployee IS NULL)
AND ([CLEC ID] IN
(SELECT [CLEC ID]
FROM CustomerRegions
WHERE [RegionID] = Forms!CLECS2MainForm!cboRegion)
OR Forms!CLECS2MainForm!cboRegion IS NULL)
AND ([CLEC ID] IN
(SELECT [CLEC ID]
FROM CustomerRegions INNER JOIN States
ON CustomerRegions.RegionID = States.RegionID
WHERE [StateID] = Forms!CLECS2MainForm!cboState)
OR Forms!CLECS2MainForm!cboState IS NULL)
ORDER BY [CLEC Name];
To restrict the form on the basis of the selections you just need to requery
it with:
Me.Requery
which you can do in the AfterUpdate event procedures of each of the
txtCustomer, cboEmployee, cboRegion and cboState controls. This will cause
the form to be progressively restricted on the basis of the selections as
each is made in the unbound controls.
If you also want the subform's restricted so that as well as the main form
being restricted on the basis of the criteria in combination each subform is
also filtered on the basis of each *individual* criterion then you then you'd
use a query which refernces the rlevant contolas a parameter as the each
subform's RecordSource property, e.g. for the employee assignments subform:
SELECT *
FROM tblEmployeeAssignments
WHERE ([EmployeeID] = Forms!CLECS2MainForm!cboEmployee
OR Forms!CLECS2MainForm!cboEmployee IS NULL);
and then also requery the subform in the AfterUpdate procedure of the
relevant unbound control, cboEmployee in this case:
Me.[qryemployeeAssignments subform].Requery
I'd suggest getting the restriction of the parent form by means of its
underlying query working first, then tackling the restriction of the subforms
if you want that also.
Ken Sheridan
Stafford, England
Number 1 is like what I want to do. Examples: 1) I would like to be able to
type in "ABC" in the company name field and have it return all records that
have ABC in their name. 2) Or type ABC and then select employee from
CboEmployee and have it return records that have ABC in the name but are only
assigned to that employee. 3) Or only select an employee in CboEmployee and
have it return all the records that have been assigned to that customer.
The main form is a list of CLEC's (customers) names with CLEC ID. There are
two subforms. The subform I'm trying to filter is based on
tblEmployeeAssignments which has Customer ID, employee ID, Title,
employeeAssignmentID.
If I can make examples 1-3 work, I would like to eventually extend the
search capability to include the other subform, which includes regions and
it's embedded subform which includes states.
So that the final solution would allow me to e.g. 4) search a customer by
employee assignment, region, state etc.
I thought the subquery would have worked. I went back to Mr.Browne's site
it said to extend this use subqueries but not much more information. But it
just seems to either make visible or not visible instead of filter.
But of course any way to accomplish would indebt me (more than I already am)!
Hope this was clear.
I'm far from clear what you are trying to do:
[quoted text clipped - 118 lines]
FullName. Data is stored in tables, and only in tables; your query will need
to reference the *TABLE* containing the data, not the form.
