Table structures for a subform within a subform?

[Glenn Brown]

I have a database with 3 main tables - projects, companies and contacts. A
contact is always linked back to the companies table. A one-to-many
relationship.

WHAT I WANT: -
1. A projects form with a companies subform, which allows users to select
various companis that are working on the project. This is a many-to-many
relationship.
2. Once the user has selected the companies which are working on the
project. I want a subform within each company (I think this is the best way?)
which enables the user to select contacts from each company which have been
selected in stage 1.

QUESTION:-
1. What should the table structure and relationship be for stage 2? I'm
thinking it should be many-to-many as well?
2. Here's my guess at a table structure - where have I gone wrong?

PROJECTS
projectID
project name....

COMPANIES
companyID
company name....

CONTACTS
contactID
contact name
companyID...

PROJECTS_COMPANIES (many-to-many relationship)
projectID
companyID

COMPANIES_CONTACTS (this is where I need help?)
projectID & companyID
contactID

Regards,
Glenn







Regards,
Glenn

 

[Graham Mandeno]

Hi Glenn

As you suspected, you are just fine up to COMPANIES_CONTACTS.

This should be PROJECTS_CONTACTS (many-to-many relationship)
projectID
contactID

You do not need to include CompanyID in this table because you can always
get a contact's CompanyID from the CONTACTS record.

Note that you cannot place a subform on a continuous form, so if you want
the companies subform to appear as a list with all the project companies
visible at once, then you will need a workaround.

The trick is to place the subforms side-by-side. Link the companies subform
in the usual way (Link Master/Child Fields = ProjectID). Then add a hidden
textbox to your main form named "txtCurrentCompany" and set its Control
Source to:
=[companies subform control name].Form!CompanyID

For your "project contacts" subform, base your recordsource on a query which
includes all the fields from PROJECTS_CONTACTS and also CompanyID and
ContactName from CONTACTS.
Set its LinkMasterFields to: ProjectID;txtCurrentCompany
and its LinkChildFields to: ProjectID;CompanyID

Then, as you select a different company from the list in the first subform,
the hidden textbox will be updated and the second subform will automatically
be refreshed with the contacts from that company who are associated with the
current project.

 

[Glenn Brown]

Thanks for your help....everything goes well until you enter a contact in
the sfmProjectsContacts subform. When you select a name with the combo box,
all the contacts in the database are shown (the filter the list to contacts
of the selected company on the sfmProjectsCompanies subform. When you select
a contact you get the following error message.

The current field must match the join key '?' in the table that serves as
the 'one' side of one-to-many relationship. Enter a record in the 'one' side
table with the desired key value, and then make the entry with the desired
join key in the 'many-only' table.

What does this mean?

When I try things with the link fields like this...it works.
LinkMasterFields to: ProjectID;txtCurrentCompany
LinkChildFields to: ProjectID;lngCompanyID

.....but like this it doesn't.
LinkMasterFields to: ProjectID;txtCurrentCompany
LinkChildFields to: ProjectID;lngCompanyID

Regards,
Glenn

Hi Glenn

As you suspected, you are just fine up to COMPANIES_CONTACTS.

This should be PROJECTS_CONTACTS (many-to-many relationship)
projectID
contactID

You do not need to include CompanyID in this table because you can always
get a contact's CompanyID from the CONTACTS record.

Note that you cannot place a subform on a continuous form, so if you want
the companies subform to appear as a list with all the project companies
visible at once, then you will need a workaround.

The trick is to place the subforms side-by-side. Link the companies subform
in the usual way (Link Master/Child Fields = ProjectID). Then add a hidden
textbox to your main form named "txtCurrentCompany" and set its Control
Source to:
=[companies subform control name].Form!CompanyID

For your "project contacts" subform, base your recordsource on a query which
includes all the fields from PROJECTS_CONTACTS and also CompanyID and
ContactName from CONTACTS.
Set its LinkMasterFields to: ProjectID;txtCurrentCompany
and its LinkChildFields to: ProjectID;CompanyID

Then, as you select a different company from the list in the first subform,
the hidden textbox will be updated and the second subform will automatically
be refreshed with the contacts from that company who are associated with the
current project.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database with 3 main tables - projects, companies and contacts. A
contact is always linked back to the companies table. A one-to-many
relationship.

WHAT I WANT: -
1. A projects form with a companies subform, which allows users to select
various companis that are working on the project. This is a many-to-many
relationship.
2. Once the user has selected the companies which are working on the
project. I want a subform within each company (I think this is the best
way?)
which enables the user to select contacts from each company which have
been
selected in stage 1.

QUESTION:-
1. What should the table structure and relationship be for stage 2? I'm
thinking it should be many-to-many as well?
2. Here's my guess at a table structure - where have I gone wrong?

PROJECTS
projectID
project name....

COMPANIES
companyID
company name....

CONTACTS
contactID
contact name
companyID...

PROJECTS_COMPANIES (many-to-many relationship)
projectID
companyID

COMPANIES_CONTACTS (this is where I need help?)
projectID & companyID
contactID

Regards,
Glenn







Regards,
Glenn

 

[Glenn Brown]

....sorry, ignore the previous message

Thanks for your help....everything goes well until you enter a contact in
the sfmProjectsContacts subform. When you select a name with the combo box,
all the contacts in the database are shown (i.e. the list is not filtered to
just show the contacts from the selected/current company on the
sfmProjectsCompanies subform. This ends up as txtCurrentCompanyID.When you
select
a contact you get the following error message.

The current field must match the join key '?' in the table that serves as
the 'one' side of one-to-many relationship. Enter a record in the 'one' side
table with the desired key value, and then make the entry with the desired
join key in the 'many-only' table.

What does this mean?

When I try things with the link fields like this...it works without the
filtered contacts.
LinkMasterFields to: ProjectID
LinkChildFields to: ProjectID

.....but like this it doesn't.
LinkMasterFields to: ProjectID;txtCurrentCompanyID
LinkChildFields to: ProjectID;lngCompanyID

Regards,
Glenn

Hi Glenn

As you suspected, you are just fine up to COMPANIES_CONTACTS.

This should be PROJECTS_CONTACTS (many-to-many relationship)
projectID
contactID

You do not need to include CompanyID in this table because you can always
get a contact's CompanyID from the CONTACTS record.

Note that you cannot place a subform on a continuous form, so if you want
the companies subform to appear as a list with all the project companies
visible at once, then you will need a workaround.

The trick is to place the subforms side-by-side. Link the companies subform
in the usual way (Link Master/Child Fields = ProjectID). Then add a hidden
textbox to your main form named "txtCurrentCompany" and set its Control
Source to:
=[companies subform control name].Form!CompanyID

For your "project contacts" subform, base your recordsource on a query which
includes all the fields from PROJECTS_CONTACTS and also CompanyID and
ContactName from CONTACTS.
Set its LinkMasterFields to: ProjectID;txtCurrentCompany
and its LinkChildFields to: ProjectID;CompanyID

Then, as you select a different company from the list in the first subform,
the hidden textbox will be updated and the second subform will automatically
be refreshed with the contacts from that company who are associated with the
current project.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database with 3 main tables - projects, companies and contacts. A
contact is always linked back to the companies table. A one-to-many
relationship.

WHAT I WANT: -
1. A projects form with a companies subform, which allows users to select
various companis that are working on the project. This is a many-to-many
relationship.
2. Once the user has selected the companies which are working on the
project. I want a subform within each company (I think this is the best
way?)
which enables the user to select contacts from each company which have
been
selected in stage 1.

QUESTION:-
1. What should the table structure and relationship be for stage 2? I'm
thinking it should be many-to-many as well?
2. Here's my guess at a table structure - where have I gone wrong?

PROJECTS
projectID
project name....

COMPANIES
companyID
company name....

CONTACTS
contactID
contact name
companyID...

PROJECTS_COMPANIES (many-to-many relationship)
projectID
companyID

COMPANIES_CONTACTS (this is where I need help?)
projectID & companyID
contactID

Regards,
Glenn







Regards,
Glenn