Populate cbo based on another cbo value

[Os]

I have a combobox for "Departments" in Access 97 Form,
and another one for "Products". Since I have many
products in the dB, I don't want the user to scroll all
the way and look for the desired product. Instead when a
user selects a department, I want the "products" combobox
to display only the items related to that department.
Currently i'm storing departments in a single-column tbl,
while the products in another table, that also include a
department field that matches each product item. Any idea

cboProducts.Value = Null

cboProducts.Requery

End Sub

cboProducts.Requery

End Sub

Thanks.

 

[tina]

base the rowSource for cboProducts on the value in the cboDepartments, as

SELECT ProductID, ProductName FROM tblProducts
WHERE DeptID = Forms!FormName!cboDepartments

requery cboProducts in the AfterUpdate event of cboDepartments, as

Me!cboProducts.Requery

substitute the correct names of your table, fields, form and controls, of
course.

hth

 

[JoeElla]

The data property of the combo box used a query. Click on the elipses of
the data property of the products combo box to enter the query defination .
Set the department combo box as the criteria for the products combo box.

 

[Os]

Thanks Tina, but it didn't work!

This is what I did:

SELECT Prod, Prod_Class
FROM Item
WHERE Dept = Forms!Main!cboDept

Then I added the:
Me!cboProd.Requery
In the "AfterUpdate" event of the cboDept as you
mentioned.
But when I select a department from the departments
combobox, all the product items are showing on the
cboProd instead of the items related to that dept only.

I do have two separate tables for Product / Dept.
Where did I go wrong?
Thanks.

 

[tina]

hmm, always hard to guess, when i can't see the db. is Item the correct name
of your products table? is Dept the correct name of the department field in
the products table? is Main the correct name of the open form? are both
combo boxes on the same form, or is one of them on a subform of the main
form?

 

[Os]

I got it to work. My Dept field was pointing to the "Dept
tbl" instead of the "Item tbl".
Thanks alot.

 

[Os]

I have another question for the same "Forma". I have
multiple cbos on the form fordate, shift, product,
dept, line, etc.) Each dept has between 12-18 lines. The
user will enter values for scheduled and actual
productivity performance each day/shift. I'd like to make
it more user friendly; Now Access will clear the entire
form once you save the record to main tbl in the dB.
Instead of having the user to select the same date,
shift, dept over and over again, I'd like it to be able
to leave some of the cbos values after saving the record.
Because the user doesn't need to change anything other
than the "Line" number plus the scheduled/actual values.
Once they done with that particular "Dept" then they can
clear the form and so on...
Can you help on how can I accomplish this?

Many thanks.

 

[tina]

i'll try, Os, but i need to give it some thought. if i or somebody else
doesn't get back to you by maybe the end of Friday (tomorrow), suggest you
post the question to a new thread.