Combo List Filters

[Glenn Brown]

I have a form (projects) with two sub-forms, companies and contacts. The
underlying tables of companies and contacts have a many-to-many relationship
with projects. What I want is; when selecting a contact from the contacts
combo list, filter the list to the selected company on the company subform.

From a previous post I was told to create a control called txtCurrentCompany
on the frmProjects from and have it’s source set to: -
=sfmProjectsCompanies.Form!lngCompanyID

For the sfmProjectsContacts subform, base the recordsource on a query which
includes all the fields from tblProjectsContacts and also lngCompanyID and
strContactName from tblContacts. And then

set its LinkMasterFields to: ProjectID;txtCurrentCompanyID
and its LinkChildFields to: ProjectID;lngCompanyID


PROBLEM: -
I 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 of course.
LinkMasterFields to: ProjectID
LinkChildFields to: ProjectID

.....but like this I get the error above.
LinkMasterFields to: ProjectID;txtCurrentCompanyID
LinkChildFields to: ProjectID;lngCompanyID

Regards,
Glenn

 

[Damian S]

Hi Glenn,

Your linking criteria only needs to include the field that links the table
together in your relationship (the PK/FK pair).

To restrict the values returned in a combo, have the query with a criteria
something like " and CompanyID = forms!frmFORMNAME.lngCompanyID" replacing
the field names as necessary.

Then, have your On Current event requery the Contacts combo (so it remains
current when the Company record changes), and requery the Contacts Combo box
if you enter a new company record.

Hope that helps.

Damian.