two comboboxes on a form: values and queries

[viennese_finger]

Hi - I have a database for Project management and project costing.

Tables are like this:

• tblProject - summarises use of SeqKit and ClsuterKit for each project
• tblClusterSeqKit - relationships between each SeqKit and the appropriate ClusterKits that go with them - there are many ClusterKits for each SeqKit
• tblSeqKit - list of SeqKits, with price and other parameters

forms:
frmProject - is from tblProject and is where I enter the project info including SeqKit.

The two parameters of interest are SeqKit and ClusterKit which are fields in tblProject.

I have set SeqKit on frmProject to be a combobox so I can drop down from values from tblSeqKit; this works fine. control = cboSeqKitSelect

Then I want the value set in SeqKit to be used as criteria for filtering the available ClusterKits from tblClusterSeqKits. I see this is as another Combobox but instead of showing all the ClusterKits in the tblClusterSeqKits table, just the ones that match up to the selected SeqKit.

Does that make sense?

I guess it would be some kind of query, but I'm not sure how to configure it.

Any help would be much appreciated.

Thanks for listening,
Matt

 

[Ranman256]

The 1st combo will filter the 2nd combo,in the AFTERUPDATE event.
Make 2 queries, 1 to show all items in the combo box
And 1 to show only items filtered with what's in cboBox1
Select * from table where [field]=forms!myForm!cboBox1

Sub cboBox1_afterupdate()
If isNull(cboBox1) then
   CboBox2. Rowsource= "qsAllRecs"
Else
    CboBox2. Rowsource= "qsFilter1Itm" 
End if 
End sub
[\code]

 

[viennese_finger]

Thanks for the reply - I think I have updated the Row Source for the 2nd combobox:

SELECT tblClusterSeqKit.ClusterKit FROM tblClusterSeqKit WHERE (((tblClusterSeqKit.ClusterKit)=[Forms]![frmRNASeqProjectPlan]![Combo654]));

running the query appears to give the correct results.

but I'm not sure where to put the code you mentioned...please could you let me know? I'm a bit of a beginner

Many thanks again

Matt