Adding criteria "ALL" to Combo or List Box

[wwce]

I have a form titled "Branch".

It contains a Combo Box titled "Manager" with a list of 5 names.

And a List Box titled "Employees" with about 30 names.

When the user selects a manager from it's drop down list, the employee list box gets populates with all the people under that manager only.

SCENARIO:

I want to add a field to select "ALL" employees, so that when the user selects "ALL" on the manager Combo Box, everyone (all 30 employees) will be populated in the employee List Box.

Is that possible?

Any help is appreciated, thanks.

 

[ranman256]

for the RUN button ON CLICK event

if cboEmp = "All" then
   docmd.openquery "qsEmpsAll"
else
   docmd.openquery "qs1Emp"
endif

 

[wwce]

for the RUN button ON CLICK event

if cboEmp = "All" then
   docmd.openquery "qsEmpsAll"
else
   docmd.openquery "qs1Emp"
endif

Hi thanks, but that's not quite it.

I was actually thinking it may be how the "Row Source" for the "Employees" List Box is coded.

So far I have it like this in SQL:

SELECT tblEmp.Employee
FROM tblEmp INNER JOIN tblMgr ON (tblEmp.Manager = tblMgr.Manager)
WHERE (((tblMgr.Manager)=IIf([Forms]![Form Branch]![Employee]="ALL","*",[Forms]![Form Branch]![Employee])));

 

[Norie]

You can use a UNION query to add 'ALL'.

How are you currently populating the combobox?

 

[wwce]

You can use a UNION query to add 'ALL'.

How are you currently populating the combobox?

Thanks, I'm not quite sure how the syntax for the UNION query should be, I just added it on the syntax to get the string "ALL".

My combo box "Manager" is basically a simple table and I'm using the Row Source command to call the list.

SELECT tblMgr.Manager
FROM tblMgr
UNION SELECT "ALL"
FROM tblMgr;

???

 

[Norie]

That looks about right, is it not working?