Filter by Form... but only for Subform

[Andante.in.Blue]

Hi everyone! I was wondering if there is a away to use Access 97's
build in filter-by-form function but restrict its effect to just the
subform.

I have a parent form that shows the major divisions of the company,
and a subform that is linked so that it displays all the jobs within
the division in the parent form. I also want the ability to filter
the subform, so the users can pick out a bunch of common jobs easily.
I did this using a simple DoCmd.DoMenuItem command button to invoke
Access's filter-by-form.

Unfortunately, when this command button is hit (or even the
filter-by-form command invoked manually), the entire form, parent and
child, goes into filter mode; what's worse, if I enter a criteria
string into the subform, and leave the main form untouched, applying
the filter causes any filter criteria I had on the main form to be
lost. To top it all off, the main form is sent to the "first"
division record, so the user is now altering characteristics of an
entirely different division!

Is there a way to prevent the filter settings on the main form from
being altered while using the filter-by-form function on the subform?
Any suggestions would be greatly appreciated.

Alan

[Allen Browne]

No. You cannot use FilterByForm like that.

If you provide your own interface (e.g. text boxes in the Form Header
section, and a command buttton to apply a filter), you can set the Filter
property of the form in the subform control. It has to look like the WHERE
clause of a query. You can mock up a query and switch to SQL View (Viw menu
in query design) if you're not sure how to achieve that. And don't forget to
set FilterOn to True as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andante.in.Blue" <05********@sneakemail.com> wrote in message
news:bc**************************@posting.google.c om...

Hi everyone! I was wondering if there is a away to use Access 97's
build in filter-by-form function but restrict its effect to just the
subform.

I have a parent form that shows the major divisions of the company,
and a subform that is linked so that it displays all the jobs within
the division in the parent form. I also want the ability to filter
the subform, so the users can pick out a bunch of common jobs easily.
I did this using a simple DoCmd.DoMenuItem command button to invoke
Access's filter-by-form.

Unfortunately, when this command button is hit (or even the
filter-by-form command invoked manually), the entire form, parent and
child, goes into filter mode; what's worse, if I enter a criteria
string into the subform, and leave the main form untouched, applying
the filter causes any filter criteria I had on the main form to be
lost. To top it all off, the main form is sent to the "first"
division record, so the user is now altering characteristics of an
entirely different division!

Is there a way to prevent the filter settings on the main form from
being altered while using the filter-by-form function on the subform?
Any suggestions would be greatly appreciated.

Alan

[Andante.in.Blue]

This is what I was afraid of. Will get started on an alternative
interface then. Thank you Allen!

Alan

"Allen Browne" <al*********@SeeSig.invalid> wrote in message news:<3f***********************@freenews.iinet.net .au>...

No. You cannot use FilterByForm like that.

If you provide your own interface (e.g. text boxes in the Form Header
section, and a command buttton to apply a filter), you can set the Filter
property of the form in the subform control. It has to look like the WHERE
clause of a query. You can mock up a query and switch to SQL View (Viw menu
in query design) if you're not sure how to achieve that. And don't forget to
set FilterOn to True as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.