Combo Box Query - Listing a subset

[Robert Neville]

The solution to my dilemma seems straight-forward, yet my mind has not
been forthcoming with a direct route. My Project form has a tab
control with multiple sub-forms; these distinct sub-forms relate
addresses (multiple addresses); companies, contacts, and tasks to each
project.

My challenge lies with the task sub-form which links to the Project
form through ProjID. No problems occur with this link; and the task
record changes when advancing to the next project. The task record
links back to the master tables through ProjID, CompID, and ContID.
In other words, the task record generally holds actions for the
Project, Company, and Contact tables; or any combination thereof.

My rationale involved creating a continuous form with combo boxes that
displays the full contact name and the full company name. The combo
boxes would allow me to choose to a Company and Contact for each Task
record. A project may have multiple task records. The ContID and
CompID fields just signify the index number value. So both combo boxes
have a query that display the full name bounded to the ID field
(example below); bound to column one. This part work fine, but the
combo box should list a subset of records for the current project
record. If you have XXX project, then companies associated with XXX
should drop down in the box; not all companies.

At this juncture, I lose my sense of direction. My ideas point towards
code or a query, yet I need coaching to move forward. The complexity
of the table relationship adds confusion. So I stopped here and wrote
this post to make sense of scenario. Each master table (Project,
Contact, or Company) links to the other tables through an intermediary
table (relationship table e.g. trelCompProj) Hopefully, someone may
lead in the right direction.

SQL Statement for the ContID Combo box
SELECT tblCont.ContID, [FirstName] & " " & [LastName] AS FullName
FROM tblCont
ORDER BY tblCont.LastName;

 

[SteveS]

The solution to my dilemma seems straight-forward, yet my mind has not
been forthcoming with a direct route. My Project form has a tab
control with multiple sub-forms; these distinct sub-forms relate
addresses (multiple addresses); companies, contacts, and tasks to each
project.

My challenge lies with the task sub-form which links to the Project
form through ProjID. No problems occur with this link; and the task
record changes when advancing to the next project. The task record
links back to the master tables through ProjID, CompID, and ContID.
In other words, the task record generally holds actions for the
Project, Company, and Contact tables; or any combination thereof.

My rationale involved creating a continuous form with combo boxes that
displays the full contact name and the full company name. The combo
boxes would allow me to choose to a Company and Contact for each Task
record. A project may have multiple task records. The ContID and
CompID fields just signify the index number value. So both combo boxes
have a query that display the full name bounded to the ID field
(example below); bound to column one. This part work fine, but the
combo box should list a subset of records for the current project
record. If you have XXX project, then companies associated with XXX
should drop down in the box; not all companies.

At this juncture, I lose my sense of direction. My ideas point towards
code or a query, yet I need coaching to move forward. The complexity
of the table relationship adds confusion. So I stopped here and wrote
this post to make sense of scenario. Each master table (Project,
Contact, or Company) links to the other tables through an intermediary
table (relationship table e.g. trelCompProj) Hopefully, someone may
lead in the right direction.

SQL Statement for the ContID Combo box
SELECT tblCont.ContID, [FirstName] & " " & [LastName] AS FullName
FROM tblCont
ORDER BY tblCont.LastName;

You need the WHERE option for the SELECT statement to limit the contacts
to a specific company; the code might be:

SELECT tblCont.ContID, [FirstName] & " " & [LastName] AS FullName
FROM tblCont
WHERE tblCont.CompID_FK = Forms!frmName!SomeControlName
ORDER BY tblCont.LastName;

You might have some Joins in there also depending on how the tables are
related.

Open the properties for the combo box and click on the query builder
(the ... to the right of the field) for the ROW SOURCE and add whatever
tables are required and drag the company field to the grid. In the
criteria row of the companyID column, add the field in the main/sub form
that has the company ID. It will look like:

Forms!frmName!SomeControlName

I'm not totally sure of your forms/subforms layout/links; you might need
a requery in the afterupdate event of the company select combo box on
the main form.

combo box should list a subset of records for the current project
record. If you have XXX project, then companies associated with XXX
should drop down in the box; not all companies.

Same idea applies here.

HTH