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
Expand|Select|Wrap|Line Numbers
- SELECT CompanyID, Company
- FROM tblCompanies
- UNION
- SELECT 0, "--ALL--"
- FROM tblCompanies
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.OpenReport...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
Expand|Select|Wrap|Line Numbers
- WHERE Forms!MyForm!MyComboBox In (0, CompanyID_fk)
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.