You can link combo boxes in a hierarchical way so that the choice made in each one limits the values displayed in the next. For example, on an order form, you can have a combo box for suppliers and a combo box for products. When you choose a supplier in the first combo, the second displays only products available from that supplier.
To do this, the second combo box's Row Source property must be set to a query or an SQL statement that refers to the first combo box. If you use an SQL statement (or a query in SQL view), the reference is placed in the WHERE clause. In the above example, the cboProducts combo box might have the following RowSource property:
SELECT Products.ProductName FROM Products WHERE Products.Manufacturer = Forms![MyForm]![cboSuppliers].Value
If you use a query in design view, the reference goes in the Criteria line. In the given example, the query would select the ProductName field, and the Criteria for that field would contain:
Forms![MyForm]![cboSuppliers].Value
There's one more step. You need the second combo box to rebuild its list whenever a new selection is made in the first combo box. To do that, create an AfterUpdate event procedure for the first combo box, and enter the following statement:
cboProducts.Requery
This technique extends well to third- and higher-level combo boxes. Say, in the given example, products are available in different styles. You could add a third combo box, cboStyles, referring to Forms![MyForm]![cboSuppliers].Value and Forms![MyForm]![cboProducts].Value. In this case, cboProducts would need an AfterUpdate event to execute the cboStyles.Requery method. You'd also want to requery cboStyles in cboSuppliers' AfterUpdate event.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.