I'm sure it is something along the lines of this FAQ but i wan to base 1 combo box depending on the table it has selected.
How do I filter combobox based on other combobox/control value
faq702-3924
Posted: Jul 22, 2003 (Edited Aug 13, 2003)
Mostly access user having problem to filter/fill combobox based on other combobox/control value. There is a simple way to do this.
Definition:
combobox/control: combobox/control: first control that has value (combox1)
combobox: second control as combobox that will fill by first control value (combox2)
Write code in to afterupdate event of combobox/control to set combobox rowsource.
Set combobox/control value to null
Set combobox rowsource properties with SQL statement, the SQL statement will have criteria in where clause to filter record from table.
And requery combobox to get up to date data.
If your form has more than one combobox that depends upon successive combobox, you can continue below process for each combobox except last combobox. And keep adding combobox value into your SQL where clause to filter unique records.
For Example,
Private Sub combox1_AfterUpdate()
combobox2= Null
combobox3= Null
combox1.RowSource = "SELECT DISTINCT Feild2 FROM Table1 " & _
" WHERE Field1='" & combox1 & "'"
combox2.Requery
End Sub
Private Sub combox2_AfterUpdate()
combobox3= Null
combox2.RowSource = "SELECT DISTINCT Feild3 FROM Table1 " & _
" WHERE Field1='" & combox1 & "' AND Field2='" & combox2 & "'"
combox3.Requery
End Sub
I hope this FAQ will help users.
From
http://www.tek-tips.com/faqs.cfm?fid=3924
