Drop downs that link together in Forms

[CP611]

I have limited Access knowledge but I'm trying to build a very basic form where there are 5 fields. I've used Combo boxes so 2 of the fields are drop downs from 2 other tables. However I want the second drop down to be dependent on the first as I'm going to have a lot of data in this database and want to segment it so when I click on the second drop down it won't bring up everything, just what is associated from the first table. Thanks

[ranman256]

the query of combo2 (cities) looks at combo1 (states) to filter.
cboCity query = qsCities1State
qsCities1State : select City from table where [state]=forms!fMyForm!cboState


you just need to refresh the next box after user picks an item in the combo...

sub cboState_AfterUpdate()
 cboCity.requery
end sub

[CP611]

hi there, thank you for your reply. I really have very limited knowledge, where would I put this code, in the form? I am basically creating a stock inventory database and have a list of products and then a category associated with those products in a table, then another table which has all the stock in/stock out transactions. I've created a form from this transactions table but want to have a drop down of category and then the second drop down where you select the stock to be taken from will be based on the category thats in the products table. thank you!

[Bob Fitz]

hi there, thank you for your reply. I really have very limited knowledge, where would I put this code, in the form? I am basically creating a stock inventory database and have a list of products and then a category associated with those products in a table, then another table which has all the stock in/stock out transactions. I've created a form from this transactions table but want to have a drop down of category and then the second drop down where you select the stock to be taken from will be based on the category thats in the products table. thank you!

This is a very common subject of enquiry. Have a search for Cascading Combos. If you post a copy of your db we may even be able to sort it out for you.

[ArviLaanemets]

Two ways to achieve same without cascading combos:
a) You have lookup tables tblProducts:ProductID, ProductName, ... and tblCategories: CategoryID, CategoryName, ... You create a table tblProductCategories: ProductCategoryID, ProductID, CategoryID. In your form's soutce table/cuery, instead of fields for ProductID and CategoryID, you have a single field ProductCategoryID. And for form you create a single combo linked to ProductCategoryID, with row source like

SELECT pc.ProductCategoryID, p.ProductName & ": " c.CategoryName As ProductOfCategory
FROM (tblProductCategories pc INNER JOIN tblProducts p ON p.ProductID = pc.ProductID) INNER JOIN rblCategories c ON c.CategoryID = pc.CategoryID'
ORDER BY 2

b) You create a table tblProductCategoryID like above, but in source table/query of your form you have fields for ProductID and CategoryID. You create a combo linked to ProductID. You create text/combo box linked to CategoryID, and set the text box either hidden or disabled (You use combo in case it will be only disabled, so user sees category name instead of it's ID. But having this control visible will be abundant, as the first combo shows both product name and it's category anyway). The main combo will have the row source like

SELECT p.ProductID, c.CategoryID, p.ProductName & ": " c.CategoryName As ProductOfCategory
FROM (tblProductCategories pc INNER JOIN tblProducts p ON p.ProductID = pc.ProductID) INNER JOIN rblCategories c ON c.CategoryID = pc.CategoryID'
ORDER BY 3

ForCombo, you create an AfterUpdate event, which writes CategoryID into hidden/disabled text box (As I myself never have tried this, I'm not sure about exact syntax here).