Adding an "All" selection to a ComboBox on Search Form

[ultimateguy]

I have a search form with unbound combo boxes that uses the combo box values as criteria for a query. The Row Source of cboCategory is based on the query:



SELECT Categories.ID, Categories.Category FROM Categories ORDER BY Categories.Category;

When I start the form and I don't select a value for the cboCategory combo box, the query just returns all the values. This is great. The problem is that if I make a selection in the combo box, I have no way of reverting it back to that initial value that would make the query return all categories - if I erase the combo box the query returns nothing. So I have to close the form and reopen it.

I tried adding an "All" value to the combobox like so:

SELECT Categories.ID, Categories.Category FROM Categories UNION Select Null as AllChoice , "(All)" as Bogus From Categories
ORDER BY Categories.Category;

but it does not seem to work. It just returns nothing instead of all the categories. How can I add a value of "All" to the combo box that will return all of the categories?

[pbaldy]

What you have should work in conjunction with this:

http://access.mvps.org/access/queries/qry0001.htm

[June7]

The first SELECT line of UNION sets the field names. Alias field names not needed in subsequent SELECT lines.

The search is using the ID field?

If the form RecordSource is a dynamic parameter that references combobox, try criteria under the ID field:

LIKE "*" & [Forms]![myForm]![myControl] & "*"

If user selects the Null (All) from the list, the LIKE and wildcard should return all records.

Will need the Requery method in code.

Paul, I am confused why your suggested criteria is to test if the control Is Null and not the field. Shouldn't it be:

[Forms]![myForm]![myControl] Or Is Null

or with the LIKE and wildcard:

LIKE "*" & [Forms]![myForm]![myControl] & "*" Or Is Null

[ultimateguy]

I used the LIKE for the criteria under the ID field and it works for Null (All), but now when I select category with ID 2, it also returns other category ID's with 2 in them, like 12, 20, 21, 22, etc.

[hapm]

Pauls suggestions is correct. If the control is null, it should return all record, there for you check the control to be null.

[pbaldy]

Paul, I am confused why your suggested criteria is to test if the control Is Null and not the field. Shouldn't it be:

[Forms]![myForm]![myControl] Or Is Null

or with the LIKE and wildcard:

LIKE "*" & [Forms]![myForm]![myControl] & "*" Or Is Null

It's an Or test. If the control is Null the test evaluates to True and all records are returned. The OP has already pointed out the flaw of using Like.

[June7]

True, the LIKE operator and wildcard not really right for number and date fields.

I never would have figured out Paul's approach. Partly because I avoid dynamic parameters in queries and RecordSource. Learned something new. Thanks!

[ultimateguy]

Thanks guys, Is Null did the trick. How do I mark a thread solved?

[pbaldy]

It's under Thread Tools at the top of the thread.