Adding "All" to the beginning of a combo box

[racefan91]

I have a form with two combo boxes that input parameters into a query to filter the information. I want to have it where if nothing is entered then the query isn't filtered at all. I couldn't figure that out so I came across the idea of adding "all" to the beginning of my combo box and then by picking "all" nothing would input as a parameter in that field. I got the all to show up in my list but when I use it no information is displayed in my query.



This is the sql for the row source i used to get the "all" to show up in my list
SELECT Tbl_Tournament.ID, Tbl_Tournament.Tournament FROM Tbl_Tournament UNION Select Null as AllChoice , "(All)" as Bogus From Tbl_Tournament
ORDER BY Tbl_Tournament.Tournament;



Any help would be greatly appreciated

[racefan91]

Conversely is there a way to have it not filter at all if there is nothing entered in the combo box?

[ipisors]

What I often do is have one query that never changes (your saved query). Then have a second query which is an empty bucket, and it has an IN() statement for your criteria. Then I code to loop through a listbox (I would recommend this instead of a combobox - it's also easy to put a checkbox right below the listbox and code it to where a user checks it, all items in listbox are selected or de-selected - label the checkbox Check/Clear All) and see which items are selected. Declare a string variable and assign to it the .Sql property of the querydef object that represents the saved query that never changes. Use the Replace() function to replace the dummy parameter values in the SQL with your In items from the listbox looping code. Then assign that final sql to the empty bucket query, then run that.

If nothing is selected, then replace the entire where statement of your sql string with nothing.

Here is an example code snippet, HTH:

aircode as best as I can recall the way I use it, don't have real life snippet on hand at the moment

dim strSQL as string
dim strIN as string
dim myVar as Variant
If me.listbox1.itemsselected.count=0 then
  strIn=""
else
  For each myVar in me.listbox1.itemsselected
           strIn="'" & me.listbox1.itemdata(myVar) & "',"
  Next myVar
End if
if right(strIn,1)="," then strIn=left(strIn,len(strIn)-1)

strSQL=CurrentDB.Querydefs("saved query name").sql
strSQL=Replace(strSQL,"Where [fieldname] In ('dummyvalue')",strIN)
currentDb.Querydefs("bucket query name").sql=strsql
'now do whatever you want with the query bucket