Getting the results for all values in a combo box

[Seth Schrock]

Introduction

A very common function that databases perform is the ability to make a selection from a combo box/list box and open a report or form based on that selection. For example, you could have a combo box that lists all the companies in your database. You choose one and find all the service requests that the chosen company has outstanding. This is very simple to do. But what if you wanted to find all outstanding service requests for ALL the companies? I have done this a few times, but I have found a new way that I think is much cleaner.

Step 1: Getting the ALL option in the combo box

In the past, the table behind my combo box couldn't have its primary key field be an auto number because I wanted the ALL value to be number 0 and auto number doesn't support that. I would then have to filter the ALL record out whenever it was used so that ALL couldn't be related to a record. Instead I now use a UNION query to append an ALL record to the row source of the search combo box, like this

SELECT CompanyID, Company
FROM tblCompanies
UNION
SELECT 0, "--ALL--"
FROM tblCompanies

This includes all the companies from my Companies table (tblCompanies) and tacks on the --ALL-- record so that the user can search for all companies.


Step 2: Setting up the query to find all values when ALL is selected

I prefer to base my reports and forms on querydefs as I think that they are easier to troubleshoot if the expected records are being found or unexpected records are being found, so my explanation of this step will refer to queries. However, this would work in any WHERE clause or criteria (ie. DoCmd.OpenRepor t...Criteria:=. ..)
So continuing with our example of finding records for the specified company or ALL companies, let's assume my combo box is called MyComboBox and is in a form named MyForm. The field that is being searched in is named CompanyID_fk and it is the foreign key in the one-to-many relationship between the Companies table and the Service Requests table. So the WHERE clause of the query would need the following criteria added to it

WHERE Forms!MyForm!MyComboBox In (0, CompanyID_fk)

Conclusion

This solution provides a way to easily find records that match all values in the combo box in a very clean and efficient way. By doing it this way, the primary key field in the Companies table can stay an Auto Number field and the --ALL-- value is only available when it is time to pull the report. It can also be added very easily after the database has been used for quite a while with no changes to the table design.

[zmbd]

Usually, I use the Union Query in the reverse order you have it to prepend a "blank" record to the row source.

Or, you can follow the article:
ACC2000: How to Include a Null Selection in a Combo Box List

The alternative is to have the value in the union (which is very similar to what is offered in OP):
Adding an (All) item to an Unbound Combo Box and have the sort set.

Then there's the vba method (note, about 1/2 way down, the union query option is offered as in the above link. I personally prefer the union query option):
How to add '(all)' to a combo box or a list box control in an Access 2000 application

and a few other methods.

[Seth Schrock]

In my implementation of this, I have an ORDER BY clause which puts the --ALL-- at the beginning of the alphabet. I left it out so that people could choose their own sorting. However, I didn't think about it that I could just switch the two sides of the UNION and put it on the top that way. Thats a good idea Z.

I'm glad that I don't have to use the VBA method to include the --ALL-- in the combo box. That is a bunch of code to do something simple. Especially when all you need to do is use the UNION query.

[TheSmileyCoder]

Its a nice way to avoid using VBA, as VBA used in queries can be a performance hit (although usually quite negligible), and also less maintenance, since there is no VBA)

[Pierkes]

Hi Seth Schrock,

It tried your code and the combobox now lists;

empty space
-All-
2013

However, on thr criteria part of my query i still have problems;

- at the start of the form "frm_trajecten" , which is a split form where you can check a few comboboaxes, i only see the records where [tr_prod_jaar] is filled with a number. I would like all the records to be visible.

All comboboxes, but [cmb_prodjr] are comboboxes that are filled using the table "tbl_trajec ten" and a specific table with for examples the names of accountmanagers "tbl_am" with the field [am_name]. When i use such a combobox i have, in the criteria part of the query a "like..." statement to have the query list all records of this particular accountmanager. This works perfectly using the code;

requery

on a button. The "split" part of the form is then populated with the right data.

However, with the new combo [cmb_prodjr] it does not work this way apparantly.

The rowsource for the combobox is now;

SELECT tr_prod_jaar  FROM Q_pijplijn_bron UNION SELECT "-All-"  FROM Q_pijplijn_bron;

where;
[tr_prod_jaar] is the field in the query where the year is (or empty)
"Q_pijplijn_bro n" is the queryname

Can you help me what to put in the criteria field so the records are sorted on the value of the combobox (or, if user chooses "-all-", it will list all recordds?

Thanks,
Pierre

[Seth Schrock]

You say you are using the "Like...". Did you try the "IN..."? I think that in your case it would be

WHERE cmd_prodjr IN (0, tr_prod_jaar)