Add "All" option to ComboBox used to filter for report in MS Access

Question

I'm trying to follow Microsoft's example on how to add an "All" option to a ComboBox in Microsoft Access, but their article does not do an adequate job of providing guidance, aside from specifying the code.

What I'm trying to do is build a form that allows a user to select an option from a ComboBox (the options are generated from records in a table), and then build a report filtered based on the user's selected option. The ComboBox consists of 2 columns: the primary key/ID of the records and their displayable names.

I can't understand the VBA code Microsoft provides enough to figure out what is going on, but I would like the "All" option in my ComboBox to either have a blank primary key/ID, or one that = 0. That isn't the case, as selecting the "All" option when using the form results in the error message "The value you entered isn't valid for this field". This leads me to believe that the "All" text is getting filled into the primary key/ID column instead of the display column. The example instructs me to assign the display column number as the "Tag" property of the ComboBox - and in this case, my display column number is 2. However, this (and pretty much any other value I add) results in the aforementioned error message.

Any idea if Microsoft's example is even applicable to my case, or do I need to adjust their code somehow?

Answer 1

Check the Control Source property of your combo box. Sounds like it may be bound to a field in the form's record source. If you make it an unbound control (nothing in the Control Source property) you should be able to select any item from the combo's Row Source without Access complaining at you.

Say your combo's Row Source is a query like this:

SELECT id, disp_name
FROM YourTable
ORDER BY disp_name;

You can add an "all" row with a UNION query:

SELECT id, disp_name
FROM YourTable
UNION ALL
SELECT TOP 1 0, "**ALL**"
FROM AnyTable
ORDER BY disp_name;

AnyTable can be just that. If you happen to have a table which contains only a single row, use that one ... and you wouldn't even need the TOP 1 part. Just try not to use some ReallyBigTable as AnyTable.

Edit: Actually some ReallyBigTable would be fine if it has a primary key or other unique field which you can use in a WHERE clause to retrieve a single row:

SELECT id, disp_name
FROM YourTable
UNION ALL
SELECT 0, "**ALL**"
FROM ReallyBigTable
WHERE pk_field = 1
ORDER BY disp_name;

UNION ALL will return all combined rows. If you have any duplicate rows, you can thin them out by using just UNION instead of UNION ALL.