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 (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 Project, Company, and Contact records; or any combination thereof.

The problem lies with combo boxes on the Task continuous sub-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 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 ABC project, then companies associated with ABC should drop down in the box; not all companies.

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 I 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 may 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!cboCompID.Requery
Forms!frmTaggingProj.Form!sfrmTaggingTasks!cboContID.Requery
End Sub

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