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:
RecID Employee 2 Andrew Fuller 9 Anne Dodsworth 3 Janet Leverling 8 Laura Callahan 4 Margaret Peacock 6 Michael Suyama 1 Nancy Davolio 7 Robert King 5 Steven Buchanan
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.)
SELECT "*" AS RecID, "(All)" AS Employee FROM Employees AS T1
SELECT T1.EmployeeID AS RecID, [FirstName] & " " & [LastName] AS Employee FROM Employees AS T1 ORDER BY Employee;
The above UNION query will produce the following results:
RecID Employee * (All) 2 Andrew Fuller 9 Anne Dodsworth 3 Janet Leverling 8 Laura Callahan 4 Margaret Peacock 6 Michael Suyama 1 Nancy Davolio 7 Robert King 5 Steven Buchanan
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. Field: [Employee ID] Table: [Employees] Criteria: LIKE [Forms]![Form1]![cbo1]
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.