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.

[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!cboComp ID.Requery
Forms!frmTaggingProj.Form!sfrmTaggingTasks!cboCont ID.Requery
End Sub

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

On Fri, 06 Feb 2004 14:42:21 -0800, Robert Neville
<robert_neville@[e-mail]> wrote:

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.