Add "All" to combo box list

[tekila]

How do I implement a "All" value in the combo box list whereby it selects all the values in the list? By the way, this combo box resides in a form that allows one to generate report based on the selection.

The value selected in the combo box is used as the criteria for a query. The record source of the report that I want to generate is set to the query so that it will only display the value I have selected in the combo box.

 

[joebickley]

You can either:

1) Put a '* ' in the list so that it acts as a wild card in ur query/report.

2) If you are familiar with VBA put the word all in ur combo box and then get VBA to filter the report based on the value entered in the box. eg

function button X_onclick()
dim strfilter as string
strfilter = "[field to filter] = " & me!combobox
if me!combobox = "all" then
docmd.openreport "reportXYZ",acviewpreview
else
docmd.openreport "reportXYZ",acviewpreview,strfilter
end if
end function

In your report make sure there is no filters or criteria in ur queries.

Hope this helps

 

[tekila]

Do I replace [field to filter] with the name of the texbox in the report eg. [name]?
You mentioned that there shouldn't be any criteria in the query that the form is bound to, how about setting criteria to those fields not in the function eg. The criteria for Date field is bound to the date selected in the form?

 

[tekila]

Since I'm using a control(combo box) on a form as a parameter for my query. I've defined the criteria in the query as

Forms!myForm!myControl

I found that if the field is left blank, all records are returned by the query when I change the criteria in the query to:

Forms!myForm!myControl OR forms!myForm!myControl Is Null

However, I prefer adding "All" to the control list but in the afterupdate event of this control, returns a null value to the criteria of the query (with "All" remaining in the control). This is to prevent user from missing out the entry of the control before hitting preview report command.

Any idea?