Multiple Subform Filtering Based on Master Query

[DevastatioN]

Hello,

Let's say I have a master form, which has two One-to-Many continuous subforms. If I wanted to be able to filter the results based on subform values (for example, show all master records which has a subform record equal to 'Product Name', this way I can filter out all 'Companies' that have only the certain product I'm looking for). Usually I would make the query which drives the master form have the parent table, and the child table with an INNER join.

However, what if I wanted to do this for two subforms. I cannot base a query off of two separate one-to-many relationships using INNER joins, because the query doesn't react as it should obviously.

If I only allowed the users to do a search on one subform, or the other subform, I believe I can make two combined queries (Parent and Child Number 1, Parent and Child Number 2) and then change the recordsource based on which subform they chose to search on, and then apply the filter.

But what if they wanted to filter both subforms together. The example I listed above would be some type of company database, which isn't my real life possible example, but it applies just the same.

Overall Example:

tblCompanies has many in tblProducts
tblCompanies has many in tblPaymentOptions

So let's say I have a main form for Companies, one subform that lists all products, and one subform that lists all possible payment options. Combining all three tables in a query doesn't work correctly to be able to base a form off of. What if the user wanted to filter the form for all companies which has Product A, and accepts PaymentOption VISA?

 

[Uncle Gizmo]

So if I understand your question correctly, you want to select one item from each subform (a row of data) from that row on each subform identify one column value, one would be a product and the other would be a payment type, use that information to filter the records returned by the mainform by modifying the main forms recordsource.

At first I didn't think it would be possible, because you would have to select one record in one subform, move to the other subform and this selection would then be lost.

However there are two events which relate to the subform window, (the window in which your sub form resides) enter, and exit. You could probably use one or both of these to store the value for the selected row for use in compiling your query.

A few years ago I recalled trying to use these events with little success, so I'm a bit apprehensive about the possibility of it working.

I would do some simple experiments to see if it's possible to use these events to extract the information you want, I would suggest you extract the information using a command button first as I know this works! Once you have it working then try and wire it up to a different event.

 

[DevastatioN]

Thanks for the reply,

I may not have explained the issue very well in the initial post. So I will try a bit more detail here.

tblCompany has the fields CompanyID, CompanyName, BillingAddress let's say.

tblPaymentType has fields PaymentID, CompanyID (Foreign Key), PaymentOption

tblProduct has fields ProductID, CompanyID (Foreign Key), ProductName

Now let's say I make a form out of this. The main part of the form is built on tblCompany, and has two subforms. One is tblPaymentType which would list all payment options, the other subform is based on tblProduct which lists all of the products they carry.

Now I make custom forms to do searches with. Which sets the Form.Filter = "blah" and Form.filteron = true. Is there a way that the form filter can filter results in a subform?

In my past experience, instead of basing this form off of the tables, I would create a query which uses an INNER join between let's say tblCompany and tblProduct, and base the form off of that query, but that would be for one single subform. This scenario has two subforms, and joining two child tables by inner joins in a query does not yield good results. A bit further, if I do a query with master table and child table, the Form Wizard autopicks up that there should be a subform... but when adding both child tables (which yields disasterous results), the form wizard has no clue what I want.

Hope this helps clarify the exact issue.

 

[Uncle Gizmo]

tblCompany has the fields CompanyID, CompanyName, BillingAddress let's say.

tblPaymentType has fields PaymentID, CompanyID (Foreign Key), PaymentOption

tblProduct has fields ProductID, CompanyID (Foreign Key), ProductName

Now let's say I make a form out of this. The main part of the form is built on tblCompany, and has two subforms. One is tblPaymentType which would list all payment options, the other subform is based on tblProduct which lists all of the products they carry.

So let's say I have a main form for Companies, one subform that lists all products, and one subform that lists all possible payment options. Combining all three tables in a query doesn't work correctly to be able to base a form off of. What if the user wanted to filter the form for all companies which has Product A, and accepts PaymentOption VISA?

I have extracted the above information from both of your posts, as I understand it you want to highlight in one subform the PaymentOption, and in the other subform you want to highlight the ProductName, from these two highlighted rows you want to generate a list of companies that meet the criteria selected.

Let's assume you have two Lookup tables, one for products, and one for payment options. Let's assume that product A returns an ID of 100 and payment option visa has an ID of 200

Each sub form is based on another table, this table stores the value representing the company in one column and the value representing either the PaymentOption or the product.

So you would need to filter both these tables by the PaymentOption, and the product ID. You could then union these two results together, giving you a single list of companies which meet the criteria. You would have to include the "distinct row term" to make sure it only return the company once.

Does that sound like a plan?

 

[DevastatioN]

Sounds good to me, I"ll give these methods a try.

Thanks,

 

[DevastatioN]

After doing some testing with it, I got the two queries built, but instead of a UNION for the results, I need an INTERSECT for the results, which Access doesn't support.

I was able to add the two side queries inside the master query attached to the main table, this way only the results where all the rows are equal appear. In design view, I have tblCompanies, linked to qryProducts, linked to qryPaymentTypes.

But this query will not be able to add a new record, so I still am going to have to change the recordsource of the form before I apply the filter.

I guess this is no big deal, I will have the main recordsource for the form, and whenever the search is wanted I will change the recordsource, apply the filter, disallow additions. And when the search is reset I will take off the filter, change the recordsource back to the normal form functionality, and then allow additions again.

EDIT: This trick isn't fully working, if only one criteria is selected. But I do see the method, so my search form will just generate the appropriate recordsource query line as a string, and change the recordsource that way. This way it is completely dynamic depending on all possible search options.