Filter Main Form by fields in multiple subforms

[Guest]

Hello! I'm trying to update the records displayed in the main form by 1
field in each of 2 subforms. I have used Allen Brownes inner join technique
(thanks Allen!)successfully for each subform separately, but I can't figure
out the code to integrate the 2 searches. e.g. The user should be able to
type a keyword in a text box for the Comment field in the tblComments subform
then choose AND/OR and select an employee from a list box connected to the
employee table. Clicking search should show records where the keyword shows
up in any comment AND/OR employee is related to the Incident. The MAIN
incident table is related through a primary key to the comment table and to
the employee table.

 

[Allen Browne]

AND/OR? I take it you want the logical OR here, where the records are
included if they match either one?

You cannot use the INNER JOIN technique in the article you refer to at:
http://allenbrowne.com/ser-28.html

Instead, use a subquery in the Filter string.
This kind of thing:

strWhere = "(IncidentID IN (SELECT IncidentID FROM tblComments WHERE Comment
Like """ & [txtKeyword] & """)) OR (EmployeeID = " & [lstEmployeeID] & ")"

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Ah Yes, This seems to be the ticket. Another mystery solved.
Thank You for your website and postings!
Zip

AND/OR? I take it you want the logical OR here, where the records are
included if they match either one?

You cannot use the INNER JOIN technique in the article you refer to at:
http://allenbrowne.com/ser-28.html

Instead, use a subquery in the Filter string.
This kind of thing:

strWhere = "(IncidentID IN (SELECT IncidentID FROM tblComments WHERE Comment
Like """ & [txtKeyword] & """)) OR (EmployeeID = " & [lstEmployeeID] & ")"

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello! I'm trying to update the records displayed in the main form by 1
field in each of 2 subforms. I have used Allen Brownes inner join
technique
(thanks Allen!)successfully for each subform separately, but I can't
figure
out the code to integrate the 2 searches. e.g. The user should be able
to
type a keyword in a text box for the Comment field in the tblComments
subform
then choose AND/OR and select an employee from a list box connected to the
employee table. Clicking search should show records where the keyword
shows
up in any comment AND/OR employee is related to the Incident. The MAIN
incident table is related through a primary key to the comment table and
to
the employee table.