Qry criteria - FilterBySelection? or Subform link by FilterBySelection

[dominicdunmow]

I have a form with fields named "Product Category" and "Geography" amongst others.

There is a FiletrBySelection command button sot he form can filter by either of these 2 fields.

I also have a subform which is linked parent/child by the "Geography" field - this essentially filters the subform by "Geography".

What I'd like to do is filter the subform by which ever of the 2 fields has been used by the FilterBySelection.

Would it be best to somehow add the FilterBySelection criteria to the criteria of the underlying query behind the subform? Or can is it somehow possible to change the parent/child link according to the FilterBySelection?

Thanks

 

[Pigster14]

Well, here is a thought. From what I understand you have a main form with a subform and on the main form the user can click a button to filter on such things as Geography, etc. This is based on what is in the Geography box or the Product Category box.

Currently, the filter works on the main form, but does not flow down into the sub form.

On the query behind the sub form, you should have the Geography and Product Category field whether or not they show on your subform. I would think. Here is my thought, in the query, put criteria in the query of the fields you need. For example, on the Geography field, IIF(IsNull([Forms]![MainForm]![GeographyFieldonMainForm]), Is Not Null, [Forms]![MainForm]![GeographyFieldonMainForm])

What this would do is use the criteria from the Geography field on the main or if it is null, then find fields that are not null. However, if you have null Geography fields and want them returned, then you would have to change that. But, that's just an example.

The only thing you need to keep in mind is you will have to put a Refresh or Requery in the code. So that the new query behind the sub-form will take effect. You may be able to put it behind the button on the main form.

I hope this helps...If you need clarification, let me know.

Thanks,
Pigster14

 

[dominicdunmow]

Thanks for coming back.

I think I understand, but both the Geography and Product Cat. fields will always be populated so I don't think this would work.

I guess what would be the perfect answer would be if I could add to the criteria line of the the query something along the lines of:-

Forms!Mainform!FilterBySelection.

But I don't know whether it can be done.

 

[Pigster14]

Actually if that field is always populated, that should work.

Because in your query using the line I had it will put in criteria for either the field on the form if it is not null or if it is null, it will look for anything in the field that is not null. Which will pull all records in your case.

Thanks.

 

[dominicdunmow]

If I add this line to the criteria of both the geography and Product Cat. fields in the query, the subform will always filter by both of fields wil it not?

The problem there is that I won't get the full Product Cat. recordset for example, I'll get the Product Cat. set filtered by what ever the value is in the geography filed.... or have I got the wrong end of the stick?