Re: Subform filter restricts Parent?

[diogenes]

"Rick Brandt" <ri*********@ho tmail.comwrote in news:bPnKj.456$ %41.325
@nlpi064.nbdc.s bc.com:

diogenes wrote:

>I'm using Access 2003 and reviving some development on an application.

I have an Orders form with a Products subform. 1:n relationship
between tables.

Is there any way to programmaticall y set a Filter on the Products
subform and have it result in a filtering of the Orders form? The
forms are bound and Master/child key binding set in code.

So far I have been able to filter the subform (Products) but the
result is that the main Orders form displays blank product records
(in the subform) for those records that are filtered out (instead of
filtering out both subform and form records).

I know that it works the other way (filter main form, subform gets
filtered) but I was hoping that there is some way to go "in reverse."

Thanks for any suggestions.

You can't make a filter on the subform affect the main form, but you can
apply a filter to the main form that finds records containing certain
products. You just need a filter that uses an IN() clause.

Assuming that the common field is OrderNumber use a filter like...

OrderNumber In(SELECT OrderNumber FROM Products WHERE Product =
'SomeProduct')

Note: I have had cases where such a filter made the data in the form
non-editable.

I used this approach, and it works a treat!

ID In(SELECT Order_ID FROM orderitems WHERE NAME = 'product')

I've not used an In clause before. Thanks a lot for the education.