Create subform from M-M relationship

[metrodub]

I have a database with three tables:

tblCompany
CompanyID (PK)
CompanyName
Address
Address2
City
State
Zip

tblPartners
PartnerID (PK)
PFName
PLName
Address
Address2
City
State
Zip
Email
Phone

tblCompany_Partners
CompanyID (PK)
PartnerID (PK)

The reason for the many to many relationship is that some partners are employed by more than one company (ex. Bob Jones works for Corp. Marketing and as an independent contractor under the guise of Bob Jones, Inc.).

When creating a form, I have a combobox with a list of the companies. When you choose a company, the appropriate address shows, etc.

What I'm having difficulty with is creating a subform that will show the partners for that company. The main form is based off of the company table. How do I go about getting a subform that will change when the user chooses a different company? Base the subform off a query? What's the proper child/parent relationship in this situation?

Thanks in advance.

 

[Frink]

Hallo,

You have to change the recordsource of the subform to be a query which links tblCompany_Partners and tblPartners.
Then the forms are linked by CompanyID.

- Frink

 

[metrodub]

This still does not allow the form to do what I want it to do.

This may be due to the fact that I created the dropdown first and therefore having a hard time with linking the subform.

The combobox is unbound and has a rowsource query pulling from tblCompany. (Maybe I should pull the CompanyID from tblCompany_Partners and not tblCompany?) The address data is filled into textboxes below the combobox. Their control source comes from the hidden columns in the combobox.

When I choose a company from the combobox, the subform does not change, even when the links are made.

 

[DomFino]

I think the example at this site will do what you want.

http://www.helenfeddema.com/access.htm

Then scroll down to number 79 to download the sample.

79. Many-to-Many Relationships accarch79.zip

Dom