Filtering Subform

[vickiwells]

I found this question while searching on another forum, and since it states my problem exactly, I'll just copy it:
Has anyone come up with a GENERIC way of providing filtering of subforms?

For example, in the Northwind database, the Orders form has an Orders detail subform.

If you want to filter for only records that contain a particular product on the subform, Access does not apply the appropriate filtering on the main form, such that your main form always contains the entire record set, thus rendering filtering of subforms completely useless.

If you have a large database with many subforms, including nested subforms, there's no practical way, that I know of, to provide the end user with the same "filter by selection" functionality that Access provides in a single form environment.

I was hoping someone may have or know of an add-in that would provide RIGHT Click FilterbySelection access to subforms, such that it would adjust the recordsources appropriately to limit the main form to only those records that contain the items on the subform. (The same way that it functions in a single form environment).

I find this to be a very limiting omission on Microsoft's part and makes it difficult for the end user to find information when subforms are used.

Do you have any suggestions or knowledge of any existing add-ins or utilities that would provide this subform filtering capability?

 

[leadhilldog]

If only I knew the answer to this! I've been wrecking my brain trying to figure out a way to do this. Please, there must be someone out there... How do u filter a main form's records by data on the subform??

 

[Laocon]

Surely there is a reverse way of doing this:

Say you have a Form/Subform sourced from a query that you want to filter by product.

Copy the Query that is sourced and rename it.

Copy the Form/Subform renaming them too - make the new version source the new query.

Create a form with a combo-box sourcing the product list. Now add a button to the form - set to open the new Form/Subform.

Open the query and add the Product field - right click the 1st criteria line and select build - choose the combo-box on the form you just made.. make sure the show box is unticked....
Right-click the 2nd criteria line and select build - choose the combo-box on the form again and add IS NULL (This makes sure that if someone opens the form without specifying product they get all products).

Now when you choose an option, and hit the button - the form is opened but the data is filtered by the combo-box...

Just an idea

[This message has been edited by Laocon (edited 08-29-2001).]

 

[leadhilldog]

Ok, that would work but what if you have several combo boxes? How can I show records from the main form with the properties selected in the combo boxes when this data is stored in the subform?

 

[Laocon]

Since the Form/Subform specify the same parameter query you can add as many criteria as you like - but the parameter query must account for every possibilty in the criteria lines:

EG
One parameter: 'A' - would need 2 criteria lines:
User filters A
A IS NULL

Two parameters: A and B - would need 4 criteria lines:
User filters by A and User filters by B
User filters by A and B IS NULL
User filters by B and A IS NULL
A IS NULL and B IS NULL

Three parameters: A and B and C - would need 8 criteria lines:
User filters by A and User filters by B and User filters by C
User filters by A and User filters by B and C IS NULL
User filters by A and User filters by C and B IS NULL
User filters by B and User filters by C and A IS NULL
User filters by A and B IS NULL and C IS NULL
User filters by B and A IS NULL and C IS NULL
User filters by C and A IS NULL and B IS NULL
A IS NULL and B IS NULL and C IS NULL

Does this make it clearer? Or would you like an example ?

 

[Rich]

I must have missed this one when originally posted, to suggest that Microsoft is at fault is equivalant to suggesting that a plumber should come around and do the dishes simply because he fitted the sink. You have to do some things yourself and this is one of applying logical thought.
If the Northwind orders main form shows the products supplied as part of the order and you wish to see all orders that contain that particular product then you have to reverse the joins in a query. In other words the product becomes the One and the order becomes the Many. The easiest way to do this is to create new query/form/subform and set reference to the selected product on the form via the query.
HTH

 

[vickiwells]

Rich, I might never have expected Access to provide an easier way do this apparently gargantuan task if it weren't for the fact that my previous database software (Approach, also a relational database) managed it without any effort on my part at all. If forms were properly joined, I could (without any coding) filter at will from anywhere I wanted: forms, subforms, worksheets and EVEN REPORTS. Though Access definitely does a great many other things better, I guess I just got spoiled on this issue and took it for granted that it was a basic task.
For you other guys, try the coding at the site below. I haven't been able to get it to work, but you might. I'm not very good at coding unless it's pretty straightforward where I can just copy and replace. Good luck. http://odyssey.apana.org.au/~abrowne/ser-28.html

 

[Alexandre]

I believe you can achieve this by changing the recordsource of your form to an SQL statement with an INNER join to the underlying table ofg your subform. Sounds awfull but it is quite simple. You may want to adapt the following according to how the user selects the filtering criteria on you subform and the type of the field used as a criteria:


You have cbox called Criteria1 and Criteria2 on your subform, where the user chooses the filtering criteria. In the after update event, you change the recordsource of the main form depending on the value typo of Criteria1 and Criteria2 (here supposed to be numbers):

If [Criteria1] is null and [Criteria2] is null then
Forms!MainFormName.RecordSource = "MainTableName"
Else
srtQuery = "SELECT DISTINCT ROW MainTableName.* FROM MainTableName " & _
"INNER JOIN SubformTableName ON " & _
"MainTableName.PrimaryKey = SubformTableName.ForeignKey" & _
"WHERE SubformTableName.CriteriaField1 LIKE " & [Criteria1] & _
"*'AND SubformTableName.CriteriaField2 LIKE " & [Criteria2] & "*' ;"
Forms!MainFormName.RecordSource = strQuery
End If


May have to check the syntax, but this the idea.

Hope this helps.

Alex

[This message has been edited by Alexandre (edited 08-29-2001).]

I just reworked a bit the SQl to show how to combine various criteria iven if the cbox are void.

[This message has been edited by Alexandre (edited 08-29-2001).]

 

[Rich]

Isn't it easier then to use seperate forms etc for the purpose?