Creating an All option in a combo box that selects from a table

[John Harrington]

Thought I’d share this with the group. I had asked the question
earlier in another forum and didn't receive a response.

What I was wondering is can you add an "All" option to a combo box
that selects its options from a field in a table? I pieced the
following solution together. Excuse me if it's obvious, but it wasn't
to me. I hope it's correct. I am using Access 2007, FWIW and am a
newbie.

Suppose you’ve created a combo box that selects from a field in a
table tblFoo. If you use the wizard to create the combo box and
specify its options and the order, Access puts the following in your
Row Source:

SELECT [tblFoo].[ID], [tblFoo].[myfield] FROM tblFoo ORDER BY
[myfield];

Then you want to add a UNION to that, before the ORDER BY, like:

SELECT [tblFoo].[ID], [tblFoo].[myfield] FROM tblFoo UNION SELECT
0, "<all>" FROM tblFoo ORDER BY [myfield];

This gives you a combo box with all the items in myfield from tblFoo
and an <all> selection at the top (ordered at the top because "<"
comes first alphanumerically).

Then, in your query, you put the following in the criteria:

Like "*" & Replace([Forms]![MyForm]![cboMyBox],"<all>","")


Kevin