<b>Help filtering subform AND main form with one operation</b>

[tmpark]

Please help me if you can.

I have a database with one main table (tblPROJECT) left outer joined to 5 child tables by PROJpkey. The main form's control source is tblPROJECT and then there are 5 separate subforms for each of the 5 child tables one of which being tblPROPERTY/frmPROPERTY.

While looking at the main form and one subform, I'd like to be able to filter either of the forms. Currently I can filter the main form just fine but if I go in and filter the subform I end up with all of the main records but only the filtered subform records visible. I have to scroll through all of the main records to see the results of the subform filter.

Is there a simple way to set it up so that the main record is filtered by the subform filter results too. I tried to change the join to 1:1 but didn't seem to see any difference.

Thanks for any recomendations you may have.

Trent

 

[Trendsetter]

The logic of a subform is that it allows you to view any number of details pertaining to the subject of the main form... Orders & the items
Customers & their orders
Teams & the players
etc.. etc..
therefore if you change the content of the subform the details in the mainform should be the same and not vary according to the contemt of the subform.

Or am I missing the point???

 

[tmpark]

Thanks Trendsetter. I guess what I'm asking is how I can filter (taken from your example) a specific player (subform) and have it return only the teams (main form) and players that satisfy the filter. Currently if I filter the players I get all of the teams but when I scroll through the teams the players are successfully filtered.

Does this make sense? Thanks for taking the time help me.

Trent

 

[Trendsetter]

Does this make sense? Not really...
In the subform if you highlight Beckham you would only get Manchester United as the mainform. That is who he plays for currently.
Do you want to know:
a) who he has played for in the past

b) other teams that have players of the same name
or
c) more information about Beckham

 

[tmpark]

I think this maybe where I'm having trouble because it seems like it should work the way you are describing. If I filter for *bec* I would expect it to return Manchester (main form) and Beckham (subform) but then also other teams and players that met that criteria. Instead what I get is all of the teams but only the players (subforms) that meet that criteria. The teams which have no qualifying players show up but just have a blank subform. I want the filter to not show teams with no matching subforms. Is that clearer?

 

[Trendsetter]

You are trying to go the wrong way up a relationship!
The relationship is termed one to many which means that one team can have many players.

You are trying to use that relationship in reverse and say how many teams have this single record (player name)available.

What you must understand is that at the time you are trying to interrogate the subform - the mainform has only get one record available to it - the one for the many...

You can the information but not in the form structure you are using.

Is this clearer???

 

[tmpark]

No. That's not it. My relationship is one to many with the main form one and the subform many. And I understand that. I'm not trying to query for that one player on many different teams- I'm trying to filter for players and teams based on the player information. I'm not getting through and I'm not sure how to make it more clear.

Specifically:
I have tblPROJECT (mainform) and tblPROPERTY (subform)linked by PROJpkey with a one:many join. When I filter the property subform for *cherry* (street name) It will allow me to see all of the PROJECTS but the properties will be blank unless they match the filter requirement (*cherry st*)

I want to only see the PROJECTS with visible properties. Is that so wrong? Thanks

 

[Trendsetter]

if you make a query with tblTeam & tblPlayers with a join based on TeamID in each table.
Make the join so that you only bring up entries that have related data in each table for the join field.

if TeamID has no record in tblPlayers then that record will not appear at all - this can now be the basis of a form that will show the info but you MUST populate the parameter
at the open form stage and NOT when you have already made a recordset based on the Team.

Am I getting there????

 

[tmpark]

I understand that... but I've already got a pretty complicated form with about 5 subforms and I don't want to have to answer parameters for each field everytime I open the form. I just want to be able to filter the records based on subform attributes. It almost works except it is not blocking the main form records when there is not a matching subform record...even when I go back and change the joins to 1:1