Sync form with multi many-to-many, multi-tabbed, multi-subforms

[NPeirce]

Hi.
I have a fairly complex database that I have to alter. For the purpose of this question, I'll simplify...

I have the relationships between tables set up.

I have a main table, tblCompanies, and two related tables, tblPeople and tblPlaces which each relate to companies in many-to-many relationship, but do not relate to each other.

Becuase there are two many-to-many relationships, I have "buffer" or "relationship" tables that maintain those linkages, tblCoAndPlaces and tblCoAndPeople.

I have a form with two tab controls with three pages each, so that I can select any combination of Companies, People, and Places in each tab control.

The upper tab control's fields are just the form's recordsource.

The lower tab control's fields are in subforms, one for each tab, and most of the tab combinations work fine by having the subform's recordsource be a query that combines for example companies and people or combines companies and places.

The problem is, when I try to have a subform display people and places, nothing is displayed no matter how I seem to set the recordsources, parent/child subform linkages, and query.

I think the problem has something to do with the two many-to-many relationships. After all, People and Places only relate to each other through Companies.

So, how can I display, for example, People in the main form and Places in the subform?

Thanks very much.

 

[MajP]

Not sure of the relationship that you want to show. Do you want to show all the places related to a company that are related to a person?

Then join tblCoAndPlaces to tblCoandPeople by CompanyID. join that to tblplaces by the place ID.

That query should be the recordsource of the subform. Link that to the person ID of the mainform

 

[NPeirce]

Oh, right...
Yes, I was looking to show all of the places (in the subform) related to the companies (not displayed here) that are related to a person (in the main form).

That advice got me pointed in the right direction and I think I've got it working now.

Thanks very much for your help!