How to add 'ALL' to List Box or Combo Box.

Quick answer is this: Use a Union Query for the RowSource of the control. Then use the LIKE operator with the criteria.

Example:
SETTING UP THE UNION QUERY
In the Northwind database there exists the Employees table. Let's say we have the following SQL statement (query) for the RowSource to display the Employees in our control. That control name is cbo1 within the form called Form1.
(*Note that the table has been Aliased to T1, the EmployeeID aliased to RecID, and there is a calculated expression concatenating the FirstName and LastName of the Employeed which is aliased as Employee.)

SELECT T1.EmployeeID AS RecID, [FirstName] & " " & [LastName] AS Employee
FROM Employees AS T1
ORDER BY [FirstName] & " " & [LastName];

Following is the result of that query:


Now add another select query to UNION with the existing query (shown in bold). Note that the world 'All' is in parenthesis. This help it to be sorted to the top of the list. Also, we're using the wild card "*" for the RecID. Finally, the ORDER BY clause was changed to the alias name of Employee (the field to be sorted). (Changes shown in green.)

[color green]
SELECT "*" AS RecID, "(All)" AS Employee
FROM Employees AS T1

UNION[/color]

SELECT T1.EmployeeID AS RecID, [FirstName] & " " & [LastName] AS Employee
FROM Employees AS T1
ORDER BY [color green]Employee[/color];


The above UNION query will produce the following results:


SETTING UP THE CRITERIA
In the second query, where the combo box or list box is being used for as the criteria, we'll set up the criteria with the LIKE operator.


SUMMARY
The UNION query in the control's RowSource property adds the wild card value and the word 'All' to the output list.

The second query's criteria will be based upon that given value (if it was a record id) or be based upon the wild card. Because the wild card value is used, the LIKE operator was needed.