You know those multiple choice tests where the last option is, "All of the above"? Sometimes you want to provide your users that same functionality in an Access combo box.
There are two ways to provide this feature.
The Simple Way
The simplest way is to throw a little label under your combo box that says, "Leave blank for all."
I find I use this feature most often when I'm providing a form to let users run a report. Here's an example from a custom Accounts Payable program we wrote:
The Slightly More Involved Way
Let's say you want your combo box to display a "[Show All]" option:
If you want to add an actual row to the combo box, you can easily do that with a UNION query.
In SQL Server, you can create a "table-less" union query:
Notice how there is no table name included in the first SELECT statement above. In Access, you can write table-less SELECT queries and you can write UNION queries, but you can't write UNION queries that include table-less SELECT queries. (Sort of like how you can pick your friends and you can pick your nose, but you can't pick your friend's nose.)
MS Access (Jet/ACE)
If you try to use the same query in Access as we did in SQL Server above, you will get the following error:
The solution, then, is to ensure the first SELECT statement in the UNION query returns only a single row. Some people do this with a "TOP 1" or "DISTINCT" clause paired with a system table that is guaranteed to be in the database, like MSysObjects.
For the sake of performance and to avoid potential permissions issues when reading system tables, I prefer to use a custom table that's guaranteed to have only a single row:
Public Sub CreateDualTable() Dim strSql As String strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);" Debug.Print strSql CurrentProject.Connection.Execute strSql strSql = "INSERT INTO Dual (id) VALUES (1);" Debug.Print strSql CurrentProject.Connection.Execute strSql strSql = "ALTER TABLE Dual" & vbNewLine & _ vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _ vbTab & "CHECK (" & vbNewLine & _ vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _ vbTab & vbTab & ");" Debug.Print strSql CurrentProject.Connection.Execute strSql End Sub
Run the above code to create a table that is guaranteed to have one–and only one–record in it. With this table, we can make a simple adjustment to our SQL Server query to get it to run in Access:
SELECT 0 AS AccountID, "[Show All]" AS AcctDesc FROM Dual UNION ALL SELECT Account.AccountID, Account.AcctDesc FROM Account ORDER BY AcctDesc;
Sorting the Catch-all Option
In the examples above, the catch-all option sorts to the top of the list because the square bracket comes before the letter "A" when sorted alphabetically.
You won't always be able to rely on that possibility, though. Luckily, we can simply add another literal field to our SELECT statements to enforce whatever sort order we want.
Let's re-write the above query to use an explicit sort order:
SELECT 0 AS AccountID, "[Show All]" AS AcctDesc, 1 AS SortOrder FROM Dual UNION ALL SELECT Account.AccountID, Account.AcctDesc, 2 As SortOrder FROM Account ORDER BY SortOrder DESC, AcctDesc;
Multiple Catch-all Values
What if we want to add another value? Just keep stacking UNION ALL statements:
SELECT -1 AS AccountID, "[Show None]" AS AcctDesc, 0 AS SortOrder FROM Dual UNION ALL SELECT 0 AS AccountID, "[Show All]" AS AcctDesc, 1 AS SortOrder FROM Dual UNION ALL SELECT Account.AccountID, Account.AcctDesc, 2 As SortOrder FROM Account ORDER BY SortOrder, AcctDesc;
Why UNION ALL?
One final note about performance.
There are two ways to join SELECT statements in a UNION query:
UNION ALL. The main difference is that
UNION will check for duplicates among the SELECT statements and remove them automatically.
That's a handy feature if you need it, but it comes at a cost. The database engine has to do some amount of work to check for those duplicates.
Therefore, if you know your UNION query won't generate any duplicates, you can squeeze a bit of extra performance out of the database engine by telling it not to worry about checking for them. The UNION ALL does just that.
Will it make a noticeable difference? Almost certainly not. But it doesn't hurt, either.