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;

 

[Tom Ellison]

Dear Robert:

The following refers to how to build what you want in a typical MDB
application.

Each project has zero or more contacts, and each contact can be used
in zero or more projects. Do I have that right. So you have created
a junction table referencing both tables to create a many-to-many
relationship. Sounds quite standard.

So, when you open the list of a combo box to show contacts found for a
project, you want to see only those contacts already associated with
that project through its junction table. Sounds right to me again.

Assuming you have a control on your continuous form for the ProjID,
the query for the combo box's RowSource can reference the value in the
ProjID control (the control bound to the ProjID column) to filter the
junction table for the desired Contacts. Doesn't that sound right?

To reference a control, use a syntax like:

[Forms]![YourFormName]![YourControlName]

Perhaps you do not wish to show ProjID on the form. Place the bound
control in the continuous form anyway, and bind it to the column.
Make its width zero, and make sure it is not in the tab order. You
can then reference it in the query while allowing it no other
function.

Your continuous form has many values in the column for this ProjID
control, but the value referenced in this way will be the one in the
currently selected row. If your form allows additions, then you will
need to adjust the query to show the correct list of Contacts for a
new row. If that new row can only reference and existing Project,
then there would be no problem anyway.

I hope this has given you some idea how to do it. I especially hope I
have understood your problem well enough this may be at least
partially helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

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;

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Robert Neville]

Tom,

Thanks for your assistance.

<< Each project has zero or more contacts, and each contact can be
used
in zero or more projects.>>

Yes, each project has multiple contacts; numerous companies; and
several addresses.

<<So you have created a junction table referencing both tables to
create a many-to-many relationship.>>

Right again! Let me illustrate with a quick list of the tables.

tblProj
trelProjAddr
tblAddr

tblProj
trelCompProj
tblComp

tblProj
trelContProj
tblCont

tblProj
tblTasks

<< Assuming you have a control on your continuous form for the ProjID,
the query for the combo box's RowSource can reference the value in the
ProjID control (the control bound to the ProjID column) to filter the
junction table for the desired Contacts. Doesn't that sound right?

To reference a control, use syntax like:

[Forms]![YourFormName]![YourControlName]>>

Your suggestion sounds logical, yet I am having trouble with
referencing the control. I always have trouble with the syntax. I have
tried the following syntaxes.

[Forms]![ sfrmTaggingTasks]![ txtProjID]>>

[sfrmTaggingTasks].[Forms]![txtProjID]

Main form -> frmTaggingProj
Sub-form -> sfrmTaggingTasks
Control - > txtProjID


<< Place the bound control in the continuous form anyway, and bind it
to the column.>>

The ProjID text box bounded to ProjID is on the continuous form. My
Company combo box has the follow SQL.

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

Please let me know if you have more suggestions on the syntax.

Dear Robert:

The following refers to how to build what you want in a typical MDB
application.

Each project has zero or more contacts, and each contact can be used
in zero or more projects. Do I have that right. So you have created
a junction table referencing both tables to create a many-to-many
relationship. Sounds quite standard.

So, when you open the list of a combo box to show contacts found for a
project, you want to see only those contacts already associated with
that project through its junction table. Sounds right to me again.

Assuming you have a control on your continuous form for the ProjID,
the query for the combo box's RowSource can reference the value in the
ProjID control (the control bound to the ProjID column) to filter the
junction table for the desired Contacts. Doesn't that sound right?

To reference a control, use a syntax like:

[Forms]![YourFormName]![YourControlName]

Perhaps you do not wish to show ProjID on the form. Place the bound
control in the continuous form anyway, and bind it to the column.
Make its width zero, and make sure it is not in the tab order. You
can then reference it in the query while allowing it no other
function.

Your continuous form has many values in the column for this ProjID
control, but the value referenced in this way will be the one in the
currently selected row. If your form allows additions, then you will
need to adjust the query to show the correct list of Contacts for a
new row. If that new row can only reference and existing Project,
then there would be no problem anyway.

I hope this has given you some idea how to do it. I especially hope I
have understood your problem well enough this may be at least
partially helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

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;

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Robert:

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

I would have thought it would be more like:

WHERE trelCompProj.ProjID = [Forms]![sfrmTaggingTasks]![txtProjID]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Robert:

I just realized your convention using sfrm probably means this is a
subform. I want to change my recommendation:

WHERE trelCompProj.ProjID =

[Forms]![MainFormName]![SubformControlName]![Form]![sfrmTaggingTasks]![txtProjID]

substitute for [SubformControlName] the name of the control on the
main form that contains the subform you're referencing. Use [Form]
literally.

For a subform within a subform, repeat the [SubformControlName]![Form]
for each iteration needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Robert:

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

I would have thought it would be more like:

WHERE trelCompProj.ProjID = [Forms]![sfrmTaggingTasks]![txtProjID]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Robert Neville]

Tom,

Thanks again. Your advice led me in the right direction. The following
syntax worked better for me.

[Forms]![frmTaggingProj].[sfrmTaggingTasks].[Form].[txtProjID]

Then I realized that the drop-down box would not display the data
unless a one created a new task record. So I tweaked the main form
adding a ProjID text box; then used this syntax in the query.

[Forms]![frmTaggingProj].[txtProjID]

Unfortunately, my adjustments did not fully resolve the situation.
When you advance to the next record on the main form, the combo boxes
in the task sub-form contains data on the last record. In other word,
the combo boxes do not display the contacts or companies for the
current record; but the instead show the contact and companies for the
previous record. This behavior has me perplexed. Do I need to re-query
the combo boxes somehow? Or Does the query have the wrong syntax?
Please let me know any suggestions.

Dear Robert:

I just realized your convention using sfrm probably means this is a
subform. I want to change my recommendation:

WHERE trelCompProj.ProjID =

[Forms]![MainFormName]![SubformControlName]![Form]![sfrmTaggingTasks]![txtProjID]

substitute for [SubformControlName] the name of the control on the
main form that contains the subform you're referencing. Use [Form]
literally.

For a subform within a subform, repeat the [SubformControlName]![Form]
for each iteration needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Robert:

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

I would have thought it would be more like:

WHERE trelCompProj.ProjID = [Forms]![sfrmTaggingTasks]![txtProjID]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Robert:

Yes, requery. Once the query has run, the list is in memory and will
just stay there. Requerying all combo boxes is commonly needed, so
they will recreate their sources using new information.

However, this is not optimal. If the query for a combo box takes a
couple of seconds, and especially if you ever have several of them,
this makes record navigation unnecessarily slow. So, I generally flag
the fact that combo boxes need requeried (in the Current Event) and
then perform the actual requery only when the combo box gets the
focus. I typically also reset the value in the combo box on the
Current Event. The "old" value may not even be in the list given the
new record on the form, and that can be misleading to a user.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks again. Your advice led me in the right direction. The following
syntax worked better for me.

[Forms]![frmTaggingProj].[sfrmTaggingTasks].[Form].[txtProjID]

Then I realized that the drop-down box would not display the data
unless a one created a new task record. So I tweaked the main form
adding a ProjID text box; then used this syntax in the query.

[Forms]![frmTaggingProj].[txtProjID]

Unfortunately, my adjustments did not fully resolve the situation.
When you advance to the next record on the main form, the combo boxes
in the task sub-form contains data on the last record. In other word,
the combo boxes do not display the contacts or companies for the
current record; but the instead show the contact and companies for the
previous record. This behavior has me perplexed. Do I need to re-query
the combo boxes somehow? Or Does the query have the wrong syntax?
Please let me know any suggestions.

Dear Robert:

I just realized your convention using sfrm probably means this is a
subform. I want to change my recommendation:

WHERE trelCompProj.ProjID =

[Forms]![MainFormName]![SubformControlName]![Form]![sfrmTaggingTasks]![txtProjID]

substitute for [SubformControlName] the name of the control on the
main form that contains the subform you're referencing. Use [Form]
literally.

For a subform within a subform, repeat the [SubformControlName]![Form]
for each iteration needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Robert:

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

I would have thought it would be more like:

WHERE trelCompProj.ProjID = [Forms]![sfrmTaggingTasks]![txtProjID]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Robert Neville]

Tom,

We're moving in the right direction, yet I keep encountering speed
bumps along this route. Please understand that I use the database
myself and my brain does not fluidly cross from development to usage.
So I do not realize the apparent solution without beginning this
dialogue.

First, the following code allows me to requery the combo.

Private Sub cboCompID_GotFocus()
Me!cboCompID.Requery
End Sub

The new idiosyncrasy involved the combo boxes not displaying previous
entered data. Apparently, the combo boxes include the data subset and
requerys the drop-down list, but if you close and re-open the form;
navigate through some records with data; then the combo boxes do not
display the previously entered data until you drop the list down.

Please let me know your thoughts on this situation.

Dear Robert:

Yes, requery. Once the query has run, the list is in memory and will
just stay there. Requerying all combo boxes is commonly needed, so
they will recreate their sources using new information.

However, this is not optimal. If the query for a combo box takes a
couple of seconds, and especially if you ever have several of them,
this makes record navigation unnecessarily slow. So, I generally flag
the fact that combo boxes need requeried (in the Current Event) and
then perform the actual requery only when the combo box gets the
focus. I typically also reset the value in the combo box on the
Current Event. The "old" value may not even be in the list given the
new record on the form, and that can be misleading to a user.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks again. Your advice led me in the right direction. The following
syntax worked better for me.

[Forms]![frmTaggingProj].[sfrmTaggingTasks].[Form].[txtProjID]

Then I realized that the drop-down box would not display the data
unless a one created a new task record. So I tweaked the main form
adding a ProjID text box; then used this syntax in the query.

[Forms]![frmTaggingProj].[txtProjID]

Unfortunately, my adjustments did not fully resolve the situation.
When you advance to the next record on the main form, the combo boxes
in the task sub-form contains data on the last record. In other word,
the combo boxes do not display the contacts or companies for the
current record; but the instead show the contact and companies for the
previous record. This behavior has me perplexed. Do I need to re-query
the combo boxes somehow? Or Does the query have the wrong syntax?
Please let me know any suggestions.

Dear Robert:

I just realized your convention using sfrm probably means this is a
subform. I want to change my recommendation:

WHERE trelCompProj.ProjID =

[Forms]![MainFormName]![SubformControlName]![Form]![sfrmTaggingTasks]![txtProjID]

substitute for [SubformControlName] the name of the control on the
main form that contains the subform you're referencing. Use [Form]
literally.

For a subform within a subform, repeat the [SubformControlName]![Form]
for each iteration needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Dear Robert:

SELECT tblComp.CompID, tblComp.CompName, trelCompProj.ProjID
FROM tblComp INNER JOIN trelCompProj ON tblComp.CompID =
trelCompProj.CompID
WHERE (((trelCompProj.ProjID)=[sfrmTaggingTasks].[Forms]![txtProjID]))
ORDER BY tblComp.CompName;

I would have thought it would be more like:

WHERE trelCompProj.ProjID = [Forms]![sfrmTaggingTasks]![txtProjID]

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts