How to make a ListBox show filtered data

[Kuala]

I lose hair over this.

I have a company organization database.

The form has a dropdown from which one can select a branch (based on table “Branches”, located in the main Form). After click on a button it shows all employees from that branch in a grid (based on table “Employees”, located in a Subform). This is done with a “Filter” table containing the BranchID from the dropdown. Works. Good.

Now some branches have departments. No branch has the same department. I have a table “Departments”. I want a dropdown that shows me the departments only for the selected branch when I click the aforementioned button. Then I want to select the department and its ID gets stored per employee (foreign key field “DepartmentID” in the employees table). Thus it is also located in the Subform.

I can’t get this right. The dropdown always shows me ALL departments, but not filtered by branches.

I have a simple .odb to explain, but cannot upload it here. Am too new (not enough points). :-/

The dropdown likely has to filter by the initial listbox (Branches), but cannot, since it is located in the Subform employees.

How can this be solved?

Hope you can help.

[Ratslinger]

Without using macros, and based upon what I’m reading above, the only way to get the correct departments for a Branch is to move the Department SubForm under the Branch equal to the Employee SubForm. Basically same type of filtering and another button to Refresh that SubForm. Each Form/SubForm must be refreshed to display any new selection. Can’t see how your going update the Employee record with the Department info regardless where the SubForms are. With macro coding (a lot to learn if you don’t know already) no push buttons are used and actions trigger events executing the code. SQL can be used for filtering and records can be updated.