Subform filter restricts Parent?

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 programmatically 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.

Re: Subform filter restricts Parent?

I don't understand what you are trying to do... or exactly what you want to
accomplish. I especially don't understand what you expect or want to happen
when you filter the subform. I can't follow the implication of "instead of
filtering out both subform and form records" (in my use of Form with Subform
Control... all the Records displayed in the Form embedded in the Subform
Control are related to the single Record displayed in the main Form, thus,
filtering some of them out would have no effect whatever on the single
Record displayed in the main Form).

Please clarify...

Larry Linson
Microsoft Office Access MVP


"diogenes" wrote in message
news:VIfKj.6095$lt2.5221@trndny05...
> 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 programmatically 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.

Re: Subform filter restricts Parent?

Sorry for being hasty and not giving a sufficient description.

I'd like to be able to allow my users to filter on a product (subform
table) and see only the orders (main table) that have that product
associated.

I know that the subform control does not support this 'out of the box.'

I'm hoping that there is an approach - like passing a copy of RecordSet
from the filtered subform to the Parent form and creating/applying a second
filter (that referenced the set of related OrderID fields) for the Parent
form - that would work.

I was hoping that someone might have implemented something like this -
"show me all the customer orders where product X was purchased."

Thanks.


"Larry Linson" wrote in
news:oFgKj.888$4Q1.471@trnddc06:

> I don't understand what you are trying to do... or exactly what you want
> to accomplish. I especially don't understand what you expect or want to
> happen when you filter the subform. I can't follow the implication of
> "instead of filtering out both subform and form records" (in my use of
> Form with Subform Control... all the Records displayed in the Form
> embedded in the Subform Control are related to the single Record
> displayed in the main Form, thus, filtering some of them out would have
> no effect whatever on the single Record displayed in the main Form).
>
> Please clarify...
>
> Larry Linson
> Microsoft Office Access MVP
>
>
> "diogenes" wrote in message
> news:VIfKj.6095$lt2.5221@trndny05...
>> 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 programmatically 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.
>
>
>

Re: Subform filter restricts Parent?

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 programmatically 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Subform filter restricts Parent?

On Mon, 07 Apr 2008 05:01:50 GMT, diogenes wrote:

That's a tough problem to crack. I once wrote what I called
QueryByFormOnSteroids that attempted to do just that, but I was unable
to get it to production quality for the general case.
Best idea is to change the RecordSource of form and subform.

-Tom.



>Sorry for being hasty and not giving a sufficient description.
>
>I'd like to be able to allow my users to filter on a product (subform
>table) and see only the orders (main table) that have that product
>associated.
>
>I know that the subform control does not support this 'out of the box.'
>
>I'm hoping that there is an approach - like passing a copy of RecordSet
>from the filtered subform to the Parent form and creating/applying a second
>filter (that referenced the set of related OrderID fields) for the Parent
>form - that would work.
>
>I was hoping that someone might have implemented something like this -
>"show me all the customer orders where product X was purchased."
>
>Thanks.
>
>
>"Larry Linson" wrote in
>news:oFgKj.888$4Q1.471@trnddc06:
>
>> I don't understand what you are trying to do... or exactly what you want
>> to accomplish. I especially don't understand what you expect or want to
>> happen when you filter the subform. I can't follow the implication of
>> "instead of filtering out both subform and form records" (in my use of
>> Form with Subform Control... all the Records displayed in the Form
>> embedded in the Subform Control are related to the single Record
>> displayed in the main Form, thus, filtering some of them out would have
>> no effect whatever on the single Record displayed in the main Form).
>>
>> Please clarify...
>>
>> Larry Linson
>> Microsoft Office Access MVP
>>
>>
>> "diogenes" wrote in message
>> news:VIfKj.6095$lt2.5221@trndny05...
>>> 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 programmatically 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.
>>
>>
>>

Re: Subform filter restricts Parent?

"Rick Brandt" wrote in news:bPnKj.456$%41.325
@nlpi064.nbdc.sbc.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 programmatically 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.