Combo Box Query: Listing a subset - Revisited

[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 (one to many).

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

The problem lies with combo boxes on the continuous form. I bounded
these combo boxes to CompID and ContID. They display the full company
name and full contact name. The combo boxes allow me to choose to a
Company and Contact for each Task record. Remember the actual Task
sub-form is linked to the Project through ProjID. A project may have
multiple task records (one to Many).


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.

With Tom Ellison's help, I was able to create a query that listed a
subset of the data.

SQL Statement---------------------------------------------

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

--------------------------------------------------------------End

Then we added some code to requery the combo box and keep thing
current. Apparently, the combo boxes only listed the current subset
after the requery code.

Code-------------------------------------------------------------

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

--------------------------------------------------------------End

My next dilemma became apparent after closing the form and opening it
again. Upon navigating through some records with data on the Task
sub-form, the combo boxes does not display previously entered data
until you drop-down the list. Quiet frankly, I remember entering data
into this combo box and the table record has data. So where did it go?

Please understand that I use the database myself and my brain does not
fluidly cross from usage to development. So I do not realize the
apparent solution without beginning this dialogue. This solution may
be as simple as placing the requery code on the main form; yet I have
a performance hit when navigating through records. Can one avoid this
performance hit with a different approach? Hopefully, someone may
lead in the right direction. Please be as specific as possible since I
do not consider myself a full time developer.

 

[Emilia Maxim]

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 (one to many).

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

The problem lies with combo boxes on the continuous form. I bounded
these combo boxes to CompID and ContID. They display the full company

Robert,

I guess you ran into this:

PRB: Combo Box in Continuous Form Shows Incorrect Data
Article ID: Q128158

and here's the explanation:

==============
SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you select a row in a combo box in a continuous form or a form
that is open in Datasheet view, the text portion of the combo box in
other records appears empty.

CAUSE

The behavior occurs under the following conditions:
- The form is a continuous form, or it is open in Datasheet view so
that you can view multiple records.
- The combo box's RowSource property is set to a parameterized query
to limit the number of rows in the combo box based on criteria in
another field in the form.
- The combo box criteria for the current record eliminate the rows
that were selected in the other records.

Microsoft Access maintains only one query recordset for a combo box
rather than one recordset for each combo box in each record.

When the criteria for the query that the combo box is based on change,
rows that were selected in the other records may be eliminated from
the recordset. As a result, the non-BoundColumn values are no longer
available to be displayed.

RESOLUTION

Do not hide the BoundColumn field. If you want to see a different
column, use the DLookup() function or the AutoLookup technique to
display the information you want in another control on the form.
=========================

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Robert Neville]

My question was answered in the original post. I placed requery code
on the main forms OnCurrent. Fortunately, the performance hit was not
significant using today's machines. I was hoping for some insight on
the scenario.

Code-------------------------------------------------------------
Private Sub Form_Current()
Forms!frmTaggingProj.Form!sfrmTaggingTasks!cboCompID.Requery
Forms!frmTaggingProj.Form!sfrmTaggingTasks!cboContID.Requery
End Sub

--------------------------------------------------------------End

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 (one to many).

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

The problem lies with combo boxes on the continuous form. I bounded
these combo boxes to CompID and ContID. They display the full company
name and full contact name. The combo boxes allow me to choose to a
Company and Contact for each Task record. Remember the actual Task
sub-form is linked to the Project through ProjID. A project may have
multiple task records (one to Many).


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.

With Tom Ellison's help, I was able to create a query that listed a
subset of the data.

SQL Statement---------------------------------------------

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

--------------------------------------------------------------End

Then we added some code to requery the combo box and keep thing
current. Apparently, the combo boxes only listed the current subset
after the requery code.

Code-------------------------------------------------------------

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

--------------------------------------------------------------End

My next dilemma became apparent after closing the form and opening it
again. Upon navigating through some records with data on the Task
sub-form, the combo boxes does not display previously entered data
until you drop-down the list. Quiet frankly, I remember entering data
into this combo box and the table record has data. So where did it go?

Please understand that I use the database myself and my brain does not
fluidly cross from usage to development. So I do not realize the
apparent solution without beginning this dialogue. This solution may
be as simple as placing the requery code on the main form; yet I have
a performance hit when navigating through records. Can one avoid this
performance hit with a different approach? Hopefully, someone may
lead in the right direction. Please be as specific as possible since I
do not consider myself a full time developer.