Search Database to Reference Subform

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.



"KenSheridan via AccessMonster.com" wrote:

I'm far from clear what you are trying to do:

1. Do you want to filter the parent form on the basis of a value in its
underlying recordset AND on the value of any related row in the subform's
underlying recordset? This would mean that only those records in the parent
form's underlying recordset would be returned if the value in the relevant
column of its recordset matched the selected value, and at least one related
row in the subform's underlying recordset included the other selected value.
Also with this scenario, do you want the criteria to be optional, i.e. if you
select a value to filter from the parent form's underlying recordset, but not
one from the subform's underlying recordset the parent form would be filtered
on the selected value regardless of the values in the subform's underlying
recordset, and vice versa?

2. Do you want to filter the parent form on the basis of a value in its
underlying recordset OR on the value of any related row in the subform's
underlying recordset? This would mean that those records in the parent
form's underlying recordset would be returned if the value in the relevant
column of its recordset matched the selected value, along with those records
whose value did not match this value but at least one row in the subform's
underlying recordset included the other selected value.

3. I suspect its 1 you want, but in either case, you'll have to set the
Filter and FilterOn properties of the parent form, using a subquery as I
described, but depending on whether you want 1 or 2 the code would differ
slightly.

4. If you filter the parent form on the value of any related row in the
subform's underlying recordset, but as well as filtering the parent form you
also want the subform to be filtered, i.e. to show only the rows which match
the selected criterion on its recordset, then you'll need to set the Filter
and FilterOn properties of both the parent form and the subform, but the
Filter properties for each will be different string expressions.

If you can explain fully in plain English *what* you are attempting to do in
terms of the underlying real world entities reflected in the form and subform,
rather than *how* you are attempting to do it, then we might have a clearer
view of how best this can be achieved, but my gut feeling at present is that
this might more easily be done by means of parameters in the parent form's
(and subform's if you want that filtered too) query which reference the
controls on the form rather than by filtering the form. All you'd need to do
then in code would be to requery the form (and possibly subform).

And BTW when you add a control to a form the first thing to do is change its
name from something like Combo52 to cboEmployee or whatever is appropriate.
Any code or parameters which reference the control will then be far more
easily understood. This should be done before entering any code in a
control's event procedure, however, as of you change the name afterwards the
link with the code will be broken and you'll need to recreate the event
procedure.

Ken Sheridan
Stafford, England

Confused wrote:
I tried that and it does not filter any records. The subform goes blank.
But when I scroll through the records, the records that have that particular
employee appear and disappear wheren the record does not have that employee.

Also I got it to do almost the same thing with this ( The difference is that
only the employee selected becomes visible:

If Not IsNull(Me.Combo52) Then
strwhere = strwhere & " ([EmployeeID] = " & Me.Combo52 & ") AND "
End If

Also I had to change this part of the code.
Me.[qryemployeeAssignments subform].Form.Filter = strwhere
Me.[qryemployeeAssignments subform].Form.FilterOn = True

But after doing so, can no longer search what is on the main form that I
used to with this:

If Not IsNull(Me.Text58) Then
strwhere = strwhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And "

Here is the code if you know of antoher way or have a resolution to be able
to search main form and subform based on criteria. I'd be real happy if I
could just get it to filter the subform. Unfortunately, it worked perfectly
until I normalized the data adn placed part of it in the subform.

Please see Code below:

Private Sub CmdFilter_Click()
Dim strwhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.Combo52) Then
strwhere = strwhere & " ([EmployeeID] = " & Me.Combo52 & ") AND "

End If



If Not IsNull(Me.Text58) Then
strwhere = strwhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And "

End If

'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strwhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strwhere = Left$(strwhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.[qryemployeeAssignments subform].Form.Filter = strwhere
Me.[qryemployeeAssignments subform].Form.FilterOn = True


End If







My mistake; in my second example the value of the combo box should have been
concatenated into the string expression (as I did in the first), not
[quoted text clipped - 41 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.

--
Message posted via http://www.accessmonster.com

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

Confused wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

So change the recorsource of the form to the query listed? I did this by
clicking on view SQL and inputted the first block of code. is this right? I
tried that and when I open the form it pops up the different paremeter
questions i.e,. [forms]! [CLECS2MainForm]![cboemployee], [CLEC ID] etc. When
I enter the employeeID in this box it opens and doesn't filter any records.
If I don't selecet anything at least all of the records return.

I must be way off.

Maybe subforms are not supposed to be filtered?

"KenSheridan via AccessMonster.com" wrote:

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

Confused wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

Ken,

You're a genius! I went back after much effort to make the different way
work along with frustration, agony, and prayer and decided to build the
forms over. I found that in the tblemployeeAssignments, the CLEC ID had a
space between it and the CLECS table did not have a space. So I rebuilt the
form/subform after changing it. I then placed your statement along with the
original code (which I knew had to work based on well...it came from you and
the amateur late night readings on subqueries):
If Not IsNull(Me.Combo52) Then
strWhere = strWhere & _
" [CLECID] IN(SELECT [cLECID]" & _
" FROM tblemployeeAssignments" & _
" WHERE [EmployeeID] = " & Me.Combo52 & ") AND "

End If

Now the whole thing works beautifully. Thank you! Thank you! That was a
couple days spent, but I'm learning....

Sorry for the big curve ball.



I"Confused" wrote:

So change the recorsource of the form to the query listed? I did this by
clicking on view SQL and inputted the first block of code. is this right? I
tried that and when I open the form it pops up the different paremeter
questions i.e,. [forms]! [CLECS2MainForm]![cboemployee], [CLEC ID] etc. When
I enter the employeeID in this box it opens and doesn't filter any records.
If I don't selecet anything at least all of the records return.

I must be way off.

Maybe subforms are not supposed to be filtered?

"KenSheridan via AccessMonster.com" wrote:

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

Confused wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1