In a table how do I take field 1 (a drop down box) and have the selection of that field determine my options for field 2? Each of the selections in field 1 will have up to ten options to be selected in field 2, ie. field 1 combo box selection = engineerr then field 2 options are electrical, mechanical, civil, structural) and if field 1 combo box selection = real estate then field 2 options are management, developer, broker.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Link Combo Box Selections
- Thread starter bdw
- Start date
Try this<br>
In combo box two use a query builder which gets it's information from combo box 1.<br>
Say combobox 1 has suppliers in it and Combo box 2 has products which they supply.<br>
Now the relationship between the 2 is "supplierID"<br>
or this field is in both tables.<br>
<br>
so combobox box 2 "control source" will be:<br>
SELECT DISTINCTROW Products.SupplierID, Products.ProductID, Products.ProductName FROM Products WHERE (((Products.SupplierID)=[Forms]![Form2]![Combo1]));<br>
<br>
Now in the combo1 "after update" event put this code<br>
<br>
Private Sub Combo1_AfterUpdate()<br>
Me![Combo2] = ""<br>
Me![Combo2].Requery<br>
End Sub<br>
<br>
this will be what actually makes it work.<br>
<br>
Ok have fun<br>
<br>
<br>
<br>
In combo box two use a query builder which gets it's information from combo box 1.<br>
Say combobox 1 has suppliers in it and Combo box 2 has products which they supply.<br>
Now the relationship between the 2 is "supplierID"<br>
or this field is in both tables.<br>
<br>
so combobox box 2 "control source" will be:<br>
SELECT DISTINCTROW Products.SupplierID, Products.ProductID, Products.ProductName FROM Products WHERE (((Products.SupplierID)=[Forms]![Form2]![Combo1]));<br>
<br>
Now in the combo1 "after update" event put this code<br>
<br>
Private Sub Combo1_AfterUpdate()<br>
Me![Combo2] = ""<br>
Me![Combo2].Requery<br>
End Sub<br>
<br>
this will be what actually makes it work.<br>
<br>
Ok have fun<br>
<br>
<br>
<br>
Another angle on this...<br>
<br>
In 'AfterUpdate' for Combo1, if you create the 'SELECT' statement on the fly, you can assign it to the RowSource of Combo2, which will cause a Requery:<br>
<br>
Private Sub Combo1_AfterUpdate()<br>
Dim strSQL as String<br>
strSQL="SELECT * ...... " & ..... & Combo1 & ...<br>
Combo2.RowSource=strSQL<br>
End Sub<br>
<br>
Obviously, in my example, the assignment of the string 'strSQL' needs to be constructed...not the 'blah,blah,blah' that I am showing.<br>
<br>
Larry Woods<br>
<br>
In 'AfterUpdate' for Combo1, if you create the 'SELECT' statement on the fly, you can assign it to the RowSource of Combo2, which will cause a Requery:<br>
<br>
Private Sub Combo1_AfterUpdate()<br>
Dim strSQL as String<br>
strSQL="SELECT * ...... " & ..... & Combo1 & ...<br>
Combo2.RowSource=strSQL<br>
End Sub<br>
<br>
Obviously, in my example, the assignment of the string 'strSQL' needs to be constructed...not the 'blah,blah,blah' that I am showing.<br>
<br>
Larry Woods<br>
- Status
Similar threads
- Locked
- Question
