Searching within subforms

[Colin Foster]

I have a database with a main form & several subforms. I can use CTRL+F to
search the main form, but (as others have found out) this doesn't work on
subforms.
I have tried two ways around this.
The first is by selecting th efield within the subform & selecting "Filter
by Form" However, this filters the subform & I still have to go and find
which main form record has the entry that I'm looking for.
The second is to set up a query that includes all of the fields that I want
to be able to search on, then add: Like "*" &
[Forms]![frmSearch]![SearchCriteria] & "*" which is linked to a form to
provide the data to search. I need to search across every field in the query
for example Field x OR field y or.... but I'm obviously doing something
wrong with this as, without any criteria all records are shown, with
something in the searchCriteria, nothing is shown.

Then I want to have the whole result displayed in my main form/subform
format.

Am I asking too much?
Regards
Colin Foster

 

[Rick Brandt]

I have a database with a main form & several subforms. I can use CTRL+F to
search the main form, but (as others have found out) this doesn't work on
subforms.
I have tried two ways around this.
The first is by selecting th efield within the subform & selecting "Filter
by Form" However, this filters the subform & I still have to go and find
which main form record has the entry that I'm looking for.
The second is to set up a query that includes all of the fields that I want
to be able to search on, then add: Like "*" &
[Forms]![frmSearch]![SearchCriteria] & "*" which is linked to a form to
provide the data to search. I need to search across every field in the query
for example Field x OR field y or.... but I'm obviously doing something
wrong with this as, without any criteria all records are shown, with
something in the searchCriteria, nothing is shown.

Then I want to have the whole result displayed in my main form/subform
format.

You can use a subquery in the advanced filter tool. For example if I had a
main form for TraningSessions and a subform for Attendees and I wanted all
Training Sessions attended by "Bob" I would create a filter in the main
form of...

SessionID IN(Select SessionID FROM Attendees WHERE AttendeeName = "Bob")

 

[Colin Foster]

Hi Rick,
Thanks for this.
Couple more follow up questions if I may...
Where do I find the "advanced filter tool" (obvious question, probably!)
Secondly, what I need to be able to do is wherever I am within the form or
subform, to click on that field & search for what I'm after throughout the
whole form (& sub forms). Would your solution work with this?
What I am tempted to do as a "quick fix" ('though I realise that one
shouldn't use such things in Access!), is to put a CTRL+F search on each
subform as well as the main form!

Regards
Colin

I have a database with a main form & several subforms. I can use CTRL+F
to
search the main form, but (as others have found out) this doesn't work on
subforms.
I have tried two ways around this.
The first is by selecting th efield within the subform & selecting
"Filter
by Form" However, this filters the subform & I still have to go and find
which main form record has the entry that I'm looking for.
The second is to set up a query that includes all of the fields that I
want
to be able to search on, then add: Like "*" &
[Forms]![frmSearch]![SearchCriteria] & "*" which is linked to a form to
provide the data to search. I need to search across every field in the
query
for example Field x OR field y or.... but I'm obviously doing something
wrong with this as, without any criteria all records are shown, with
something in the searchCriteria, nothing is shown.

Then I want to have the whole result displayed in my main form/subform
format.

You can use a subquery in the advanced filter tool. For example if I had a
main form for TraningSessions and a subform for Attendees and I wanted all
Training Sessions attended by "Bob" I would create a filter in the main
form of...

SessionID IN(Select SessionID FROM Attendees WHERE AttendeeName = "Bob")

 

[Rick Brandt]

Hi Rick,
Thanks for this.
Couple more follow up questions if I may...
Where do I find the "advanced filter tool" (obvious question, probably!)

It's on the menu bar whenever a form is displayed. In Access 87 it's at...

Records - Filter - Advanced Filter/Sort...

You can modify the standard toolbar to add a button that will take you straight
to it.

Secondly, what I need to be able to do is wherever I am within the form or
subform, to click on that field & search for what I'm after throughout the
whole form (& sub forms). Would your solution work with this?
What I am tempted to do as a "quick fix" ('though I realise that one shouldn't
use such things in Access!), is to put a CTRL+F search on each subform as well
as the main form!

You could code your own button or shortcut menu that would do it. The code from
an event in the subform would look something like...

Me.Parent.Filter = "PrimaryKey In(Select ForeignKey FROM ChildTable WHERE
SomeField = SomeValue)"
Me.Parent.FilterOn = True

The complexity is in dealing with different datatypes and field names with a
single code routine if you want to search on multiple fields.

 

[Colin Foster]

Hi Rick,
Thanks for this. I will have a play.
I have found (from trying it out!) that you can't add CTRL+F buttons on sub
forms, so that route was out, so what I've done - short term - is to set up
a query that picks out the main details that I've been asked for, set up a
form to display this & then link the form to a button on the main form.
MAybe not totally elegant, but works!!

Once again, thanks for your help.
Regards
Colin