Filtering subforms via main form

[Valkon]

I have am Access 2007 form with several subform controls and each has the
master/child fields set. The main form has a filter based on a unbound
checkbox. These subforms work fine and the filtering works as intended on
the main form.

However, I am unable add filtering of the subforms based on a value on the
main form. I have tried several methods including:

me.subform.form.filter = strFilterCondition
me.subform.form.filteron = true
me.subform.form.requery

and

me.subform.form.recordsource = strNewSQLStatement
me.subform.form.requery
me.subform.requery

but the subforms do not get filtered. Even though the code is executing,
the filter does not seem to get set, nor does the recordsource seem to be
updated (when I switch to layout view from form view, the subforms still show
their original filters and recordsources).

I have also tried removing the master/child entries and included that
"filter" in strFilterCondition and strNewSQLStatement with no luck.

Is there something I may be overlooking?

We have several records in the main table that have the same Master_ID but
have different revision dates. The goal is to limit the subforms to only
display data where the master.revision_date >= child.revision_date (and
master_id = child's parent_id which should be taken care of with the subform
control master/child entries no?).

Thanks in advance,
Valkon

 

[Jeff Yarrington]

I am also looking for the answer to this same problem.





Valkon wrote:

Filtering subforms via main form
09-Sep-09

I have am Access 2007 form with several subform controls and each has th
master/child fields set. The main form has a filter based on a unboun
checkbox. These subforms work fine and the filtering works as intended o
the main form

However, I am unable add filtering of the subforms based on a value on th
main form. I have tried several methods including

me.subform.form.filter = strFilterConditio
me.subform.form.filteron = tru
me.subform.form.requer

an

me.subform.form.recordsource = strNewSQLStatemen
me.subform.form.requer
me.subform.requer

but the subforms do not get filtered. Even though the code is executing
the filter does not seem to get set, nor does the recordsource seem to b
updated (when I switch to layout view from form view, the subforms still sho
their original filters and recordsources)

I have also tried removing the master/child entries and included tha
"filter" in strFilterCondition and strNewSQLStatement with no luck

Is there isomething I may be overlooking

We have several records in the main table that have the same Master_ID bu
have different revision dates. The goal is to limit the subforms to onl
display data where the master.revision_date >= child.revision_date (an
master_id = child's parent_id which should be taken care of with the subfor
control master/child entries no?)

Thanks in advance
Valkon

Previous Posts In This Thread:

Filtering subforms via main form
I have am Access 2007 form with several subform controls and each has th
master/child fields set. The main form has a filter based on a unboun
checkbox. These subforms work fine and the filtering works as intended o
the main form

However, I am unable add filtering of the subforms based on a value on th
main form. I have tried several methods including

me.subform.form.filter = strFilterConditio
me.subform.form.filteron = tru
me.subform.form.requer

an

me.subform.form.recordsource = strNewSQLStatemen
me.subform.form.requer
me.subform.requer

but the subforms do not get filtered. Even though the code is executing
the filter does not seem to get set, nor does the recordsource seem to b
updated (when I switch to layout view from form view, the subforms still sho
their original filters and recordsources)

I have also tried removing the master/child entries and included tha
"filter" in strFilterCondition and strNewSQLStatement with no luck

Is there isomething I may be overlooking

We have several records in the main table that have the same Master_ID bu
have different revision dates. The goal is to limit the subforms to onl
display data where the master.revision_date >= child.revision_date (an
master_id = child's parent_id which should be taken care of with the subfor
control master/child entries no?)

Thanks in advance
Valkon


Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx

 

[Jeanette Cunningham]

Filtering is not always straight forward.
If you have a main form with a single subform and the link master and child
fields use the primary key of the main form's record source, which is also
the foreign key of the subform's record source, then filtering the main form
will automatically filter the subform (in my experience).

However if you filter using fields other than the primary key and foreign
key, it is not always so easy.

Here is what I sometimes need to do.

Assuming the filter is called strFilterCondition
----------------------------
With Me.[NameOfSubformControl].Form
Filter = ""
FilterOn = True
Filter = strFilterCondition
FilterOn = True
End With
------------------------------

Note: the subform control can have a different name from the name of the
subform inside it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia