Combo Box: select all & refresh row source

[Lisa]

Hi! I have 2 issues I'm hoping someone can help me with.

1. On a form, I have 2 combo boxes. One to select the
Division (Combo2) and another to select the Branch. The
branch combo box is linked to a query which lists the
Division and corresponding Branch Codes. In the branch
combo box I've put the following in the Row Source so that
when a particular Division is chosen only those relevant
branhes drop down in the Branch combo box.

SELECT [qry_Branch].[Branch] FROM qry_Branch WHERE
[qry_branch].[Division]=Combo2;

It seems to work when I first open the form, select a
division and then choose a branch but if I want to select
a different division after that, the drop down list does
not refresh to show the branch codes for the new division.

2. Also, I would like to know how I could have an option
for "all" in the branch combo box so that the results for
the query using the branch combo box as a selection
criteria would show all branches.

Hope I've explained this clear enough for you to
understand what I'm trying to achieve.

Thanks in advance,
Lisa

 

[Nikos Yannacopoulos]

Lisa,

1. Use the Before Update or On Change event of fo the Division combo to run
a macro or code that will Requery the second combo.

2. In your Division combo rowsource add a null (empty) row. Then modify the
SQL statement for the second combo rowsource to:
SELECT [qry_Branch].[Branch] FROM qry_Branch WHERE [qry_branch].[Division]
Like '*' & Combo2 & '*'

HTH,
Nikos