All Option in a Combo Box

[mamandeno]

I have a form that opens a query, a comob box on the form restricts records included in the query, to the department selected.

E.g if I select "GradingRoom" only records for employees who work in the grading room will be included.

The list of available departments is in a table which does not have a primary key defined.

I would like to have an option to show all records.

I have seen a combo box where a union query was included in the row source to insert all in the drop down list. There was also a table called All in the database.

Any ideas?

I could create a duplicate query with no criteria, and use this when I want all records, but this seems like uneccesary duplication.

 

[unclejoe]

Hi,

Try something like this,

SELECT txt, ordr
FROM (SELECT DISTINCT 'All' AS txt, NULL AS ordr FROM ColorTable) 
UNION SELECT colorName, colorName FROM ColorTable
ORDER BY ordr;

The list of available departments is in a table which does not have a primary key defined.

I would like to have an option to show all records.

I have seen a combo box where a union query was included in the row source to insert all in the drop down list. There was also a table called All in the database.