469,338 Members | 8,242 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Nov 16 '10 #1
10 11751
Stewart Ross
2,545 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.YourListboxName = "<All>" then
  2.   strFilter = "*"
  3. else
  4.   strFilter = "'" & Me.YourListboxName & "'"
  5. 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.

Nov 18 '10 #2
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?
Nov 18 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
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.

Nov 18 '10 #4
32,182 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [RepName]
  2. FROM     [Reps]
  3. UNION
  5.          '<All>'
  6. FROM     [Reps]
  7. ORDER BY [RepName]
Nov 18 '10 #5
The code used to execute the query is as follows:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Contracts
  3. 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!
Nov 19 '10 #6
32,182 Expert Mod 16PB
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?
Nov 19 '10 #7
Stewart Ross
2,545 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. 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 
  2. FROM Contracts 
  3. WHERE (((Contracts.SalesRep)like 
  4.          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.

Nov 19 '10 #8
32,182 Expert Mod 16PB
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.
Nov 19 '10 #9
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.
Nov 23 '10 #10
32,182 Expert Mod 16PB
You're welcome Tim.

We're around if you need to fire off more questions threads.
Nov 23 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dale Ring | last post: by
1 post views Thread by Loreen | last post: by
reply views Thread by Greg Strong | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.