| re: Combo Box Query: Listing a subset - Revisited
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@y@h0o.com> wrote:
[color=blue]
>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.[/color]
| 
