09 Jun 09 Fake item in dropdown – MS Access SQL trick

I often use a combobox to filter.

For an example, when browsing between the tasks assigned to the staff, it can be selected, which co-workers jobs should be displayed.
It occurs sometimes, that in an Access form we have to include to a combobox / dropdown list an “Any/All” item, which can be used, when we don’t want to filter according to the co-workers.

I have a stylish/interesting solution for this problem.

I create a query, which shows only the active records in alphabetical order.

It looks like this:

The joker record is not yet included.
I open the query for editing (View/SQL view).

SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
ORDER BY tblUser.username;

I change the query source to:

SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
UNION SELECT 0, 'All'
FROM tblUser
WHERE (((tblUser.userID)=1))
ORDER BY tblUser.username;

I base my virtual record on another record, but I assign its values myself.
Therefore my query returns an enlarged list.

When using on a form, I set the default value of the dropdown list to 0.

I set the dropdown’s update event that when it is set to 0 the filter omits this dropdown, and in all other cases it omits the selected one.

I hope you liked this hint.