Add "ALL" in a Combobox

[PhilRKC]

Hi,

I need to include in a Combobox the "(ALL)" option. So i can choose a specific piece of information that an associate query will use as criteria or "ALL" of the options. I know this can be done, particularly in Access97 as it is in the Help files. However it says you follow the example in Developer Solutions. When i do that it locks the program and shuts down Access. Any ideas?
Thanks

 

[FancyPrairie]

Here's an example query that adds "(All)" to the top of the list.

SELECT Table2.strName
FROM Table2 union select "(All)" from table2;

 

[PhilRKC]

Thanks for replying,

Can you be a little more specific, I do not understand what you mean.
I was under the impression that i needed to use a module. It sounds like there is a much easier way.

Thanks

Phil

 

[FancyPrairie]

I guess I misunderstood what you are trying to do. I thought you wanted a combobox to display "(All)" (as the first item in the combobox) along with the rest of your information.

Can you be more specific.

 

[PhilRKC]

OK.

In a combo box, which lists different items that can be selected, i want to be able to choose "all" of the items as an additional choice. Eg. If the combo box listed: WINTER, SPRING, SUMMER, FALL, therefore i could choose either of these which would be used as the criteria in a query. However to avoid having to write another query to show all the data I want the option of being able to add "ALL" (meaning ALL of the combo criteria, or another way to explain it would be the criteria of the query would be "", blank) to the above list. Meaning that i could choose a particular season or "ALL" seasons.
As mentioned in the Help of Access 97 it very simply explains that this can be done, however the explanation is found in "Developer Solutions". When i go to Developer Solutions the explanation is good. Part of the explanation is to Copy & Paste a module from developer solutions into the required Database. When i did that and followed all the directions and tried to run the application the program locked up and caused an error.

Note: When i go into Access 2000 help, i can not find anything on the add "ALL" to list in a combobox.

I can attach the information if required.

Thanks.

Phil

 

[FancyPrairie]

Sorry I didn't get back to you sooner. The query I gave you does whay you want. The output of the query (when modified to meet your needs) would look like this:

(All)
Winter
Summer
Sping
Fall

The "union" statement is what is adding "(All)" to your list. Here's the query again.

SELECT strSeason
FROM tblSeason union select "(All)" from tblSeason;

Now, assuming you're building the query on the fly, when the user selects "(All)", you don't bother adding it to the Where clause. Otherwise you do.

 

[MichaelBartolotta]

The problem with the above solution is that Union queries are not supported under some web browsers.
Another solution:
For this example, assume the table that contains the combo box values is ...
================
MyCriteria
----------------
MyCriteriaID - AutoNumber
MyCriteriaName - Text
================
1 - Add a record to MyCriteria, preferably ID #1, with the Name "* All Criteria *"
2 - The "*" in the name will cause it to sort to the top
3 - Use a query against this table for your combo box. HINT: NEVER use tables directly, you'll just be asking for maintenance headaches
4 - Modify all other queries that access MyCriteria to "WHERE MyCriteriaID > 1"


If you design all your lookup tables in this fashion to start, then this solution is simple and very efficient. It may be a chore to implement after the fact, but it's worth the effort.