Can I add an "ALL" option to a list/combo box and how do I do it?

[Tim Mullin]

Hello all,

Sorry if this question appears a lot, I've looked all over the internet and can't find a single solution that works.

I would like to add an "All" option to a list box (and a combo box, but I'm assuming the coding is similar enough for me to modify, if needed) that when selected and a query is run based off the selection it returns records that match all of the options in said list. The code I have right now is:

SELECT [Reps].[RepName] FROM [Reps] UNION Select "<All>" FROM [Reps] GROUP BY [Reps].[RepName];

This adds the all option to the list, but returns 0 records when used for the query... Do I need to modify the query criteria at all for this??

Any help would be greatly appreciated, thanks!

[Stewart Ross]

Hi. The UNION query is correct for adding the '<All>' option to your listbox/combo, but this is only the first part of what you need to do.

You'll then have to apply a filter. To do this you'll need to use the listbox's After Update event to respond to the user's selection, and test whether or not the '<All>' option has been selected. If it has, you'll need to substitute the wildcard character ("*" in Access) to match all text values:

If Me.YourListboxName = "<All>" then
  strFilter = "*"
else
  strFilter = "'" & Me.YourListboxName & "'"
end if

You can either use the filter string to filter the form directly (by setting and applying the form's filter property) or you can build a SQL string with the filter string forming its WHERE clause (built up using the standard SELECT..FROM syntax).

Although not done in quite the same way as described above, there is a comprehensive article in our Insights section on filtering by form which I've linked for you. It gives a lot more insight into how form filtering can be done than I can refer to here.

-Stewart

[Tim Mullin]

Thanks for the reply - Do I need to write the wildcard ("*") into the initial code for the row source for the listbox? I tried the code you provided but got nothing to return, and I think it might be on my end. Any suggestions?

[Stewart Ross]

You are mistaking what the listbox does - which is to show you the list of reps in your case (with an 'All' option unioned in), and what you do to filter the form afterwards using the value of the row selected from the listbox.

As you said in post #1, your listbox was being filled correctly with your rep names plus the All option. Thereafter, you have to make use of the After Update event to prepare an SQL statement which you can then execute to accomplish whatever it was you wanted to accomplish based on the value you have chosen from the listbox.

As you have not provided an example of the query you are trying to run I can be no more specific about this than to tell you what the steps are, and to point you back to the article I linked the last time.

What you do with the value you choose after you choose it should not mean that you change the row source for the listbox - this would only be necessary if you were cascade filtering (filtering one list box dependent on what you chose from another) and I doubt that this is what you are wanting to do.

-Stewart

[NeoPa]

Tim Mullin:
This adds the all option to the list, but returns 0 records when used for the query

To help further we would need you to post something explaining what you do for the query. Preferably the code currently used to execute it, and/or the SQL for the query itself. You're now asking about an area that we have no information about, so helping more specifically is not possible just now.

BTW your UNION SQL should work, but depends on UNION dropping duplicates. This can be inefficient for queries of large tables. Consider instead using the TOP 1 predicate instead :

SELECT   [RepName]
FROM     [Reps]
UNION
SELECT TOP 1
         '<All>'
FROM     [Reps]
ORDER BY [RepName]

[Tim Mullin]

The code used to execute the query is as follows:

SELECT Contracts.SalesRep, Contracts.ClientName, Contracts.Commodity, Contracts.Type, Contracts.SignDate, Contracts.[Renewal/New], Contracts.Fee, Contracts.Supplier, Contracts.StartDate, Contracts.Term, Contracts.EndDate, Contracts.ContractRate, Contracts.AnnualUsage, Contracts.UsageType, Contracts.Notes
FROM Contracts
WHERE (((Contracts.SalesRep)=[Forms]![QueryScreen]![List69]));

I added the "Top 1" predicate to the Union SQL, so hopefully I can get this to work with a little more insight.

I really appreciate the help from everyone, thanks!

[NeoPa]

So I guess the ListBox we've been talking about is on the form [QueryScreen] and is named[List69].

Now, for this to work, the values of both the ListBox (essentially [Reps].[RepName]) and the field you're filtering on ([Contracts].[SalesRep]) must be of the same form. Either both strings or both numeric IDs. Can you tell us the Field Types of these two entities?

[Stewart Ross]

Unless users make a selection from the listbox the reference to the listbox in your WHERE clause will result in no records being returned (as your listbox is Null until you make a selection).

Regarding the '<All>' placeholder, you will need to test for this as a value and change the WHERE clause accordingly. To help a little we can probably treat the non-selection (null) state as meaning 'All' as well.

SELECT Contracts.SalesRep, Contracts.ClientName, Contracts.Commodity, Contracts.Type, Contracts.SignDate, Contracts.[Renewal/New], Contracts.Fee, Contracts.Supplier, Contracts.StartDate, Contracts.Term, Contracts.EndDate, Contracts.ContractRate, Contracts.AnnualUsage, Contracts.UsageType, Contracts.Notes 
FROM Contracts 
WHERE (((Contracts.SalesRep)like 
         IIF(Nz([Forms]![QueryScreen]![List69], "<All>")="<All>", "*", Forms![QueryScreen]![List69])));

Note the use of the 'like' operator to allow the use of the wildcard character, "*", in place of "<All>".

Running a query this way is not the best approach - it is normal to build such a query in code in response to the After Update event of the listbox, but as you clearly have not gone that route I will not do more than mention that the code-based route gives the most flexibility.

-Stewart

[NeoPa]

Sorry. I forgot the thread was mainly about that. Your saying you got nothing probably meant you got nothing when you ran it against the <All> selection. I was interpreting it to mean you got nothing.

Stewart's response is probably more appropriate for you then. I suggest you also consider his point about preparing the SQL in code too. It need only be a Filter property that requires changing. Far simpler.

[Tim Mullin]

Stewart - this is EXACTLY what I was looking for, Thank you so much! Got it up and running on a few combo and list boxes and it works like a charm.

Thanks to everyone else too for their help, I truly appreciate it. There is no way I would be able to finish the database project without this site.

[NeoPa]

You're welcome Tim.

We're around if you need to fire off more questions threads.