Search combo box with “All” or “Any” option

Question

In MS Acccess I have a column in my table that uses a drop down menu to select the value for the field, with six possible options. I am trying to make the same drop down menu/combo box in my form so that when a particular option from the list is selected it will filter my search query. I've made it to that point and works without a problem here.

However I want to have an option in the combo box that will say "Any" or "All" which will behave in a way that all records will be shown. Like when the combo box is initially blank when nothing is selected and the query shows all the records.

I have researched on the internet a few ways to do this, but I cannot get it to work properly.

I have added the UNION to my RowSource query, which looks like this:

SELECT [testtable].[colourtype] 
FROM   [testtable] 
UNION 
SELECT "any" 
FROM   [testtable];

And I added this to my AfterUpdate event:

If Me.ColourT = "Any" Then
  strFilter = "*"
End If

What this does strangely is: it made my combo box list with blank values, there are six blank spaces (white) and no text.

EDIT: This is the code I have in my criteria for the query:

Like "*" & [Forms]![Search]![ColourT] & "*"

Can you show us all the code after you select an item from the ComboBox? The basic idea would be to set the filter criteria after you select a value. If the selected value is "Any" or whatever else would reset the criteria, then you remove the filter — Jeff
Without UNION, are you getting the colour types in your ComboBox? — Ravindra Gullapalli
@ravindraGullapalli Yes without the 'UNION' it shows the values in the combo box and can select them to filter the query. — al3
@Jeff I've added the code that I have used in the criteria for the query. I guess that needs to be modified? — al3

Ravindra Gullapalli Ravindra Gullapalli · Accepted Answer · 2013-12-27T13:03:04

Finally, I made it worked based on the suggestion given here

Constructed Query like this

SELECT [id], [colourtype] 
FROM   [testtable] 
UNION 
SELECT 0, "any" 
FROM   [testtable];

and set this query as Row source of combo box.

In control source property, choose colourtype.

Now combo box shows values. Hope this helps

Search combo box with “All” or “Any” option

1 Answers