Select all in combo box

[eyg]

Hi,
I have a combo box which shows a list of data to report on - but there are times when I want to select all the data to report back on.
How do I get the word all into the combo list to return all the information onto my report.

 

[Luddite Lad]

Have you considered a multi-select ListBox?

 

[Tim L]

Hmm,

You should be able to achieve something like what you describe, but it will, I think, require a fair bit of work.

First off you want to get 'All' as one of the options in the row source for the combo box. Whether you already have a query or not, for the row source, you are probably going to need one now as the tone of your message suggests that you have not entered a list of data for the combo box (if you had then I figure you wouldn't be asking the question) which suggests that the list will need to be dynamic, rather than fixed.

You will need to either:

• Create a JOIN query that adds a single additional record to your current row source, so that this is listed with the original list. How you ensure that 'All' is at the top of the list (perhaps the most sensible place) or even at the bottom (an alternative to the top) is up to you and dependent on what you want to acheive, Or,
• Add an additional record to the table that the row source is based on (even if there is a intermediate query, or two) but this could cause problems elsewhere (like places you do not want 'All' to appear).

Even after you have done this your work is not complete as you will probably need to add code to your form to handle the selection of 'All', you won't just be able to select 'All' from the drop-down box and let the underlying query handle. I say, probably, because if you are using the drop-down to select an item which is related to an SQL query (not shown) in the row source of the drop-down and you are using this SQL as the basis of the record source for the report, then your work may be done, but somehow I doubt it.

You will need to consider creating an addtional query to handle the 'All' situation, which you could instigate when the user requests the report (I presume that the request is formally made by clicking a command button or on the 'after update' event of the combo). It might, however, be less complicated to request 'All' from a separate button than building the 'All' option into the combo box drop-down (although I do think that building the option into the combo is the neater solution, from the users perspective).

HTH

Tim

 

[missinglinq]

As Tim says, you'll have to write code to handle the "All" option for the combobox, depending on exactly what it is you're doing, but in order to get the "All" option at the top of your combobox, replace its RowSource by with this:

SELECT YourFieldName FROM YourTableName
UNION SELECT "(All)" FROM YourTableName;

Good Luck!

 

[eyg]

Thanks for that - I think I will try with the Multiselect list box first (as I am still learning) and will try out the other coding when I am more familiar.
Thanks for all your help.

 

[Tim L]

Goog luck (and not meant sarcastically). I've been using Access now for half a dozen years and still haven't figured out about using multiple select in List Boxes, mostly because all of the advice I have seen was along the lines of 'there be dragons', so I'll be interested to see your solution.