'Serving the Microsoft® Access
Adding an ALL Choice to a Combo Box
Here is an example of how I might use a combo box for report generation.
A form opens with a Client Type combo box on it. I click on the drop down and choose a type. I then click a "Print Report" button. The report prints all clients who belong to that client type. This is very easy to setup and works nicely.
What if I want to print ALL clients on one report?
My standard "brute force" method would entail a second button which starts a separate report. Very fast and easy to create, but not elegant looking.
Another method that could be used is have a checkbox that gets checked when you want ALL clients. Better, but not the best.
For me the best solution is to have the word "ALL" show up in the list of the combo box. Looks neat and tidy to the end user of your database; in fact, it looks professional!
How do we do it?
(NOTE: this article was written based on research using Access 2000. It should work on other versions.)
(article continues after sponsor spot)
To start, I had a client table and a client type table, linked in a simple 1-to-many relationship.
Step 1. Create a form with a combo box called cboClientType. The Row Source of the combo box in my case is:
SELECT ClientType, ClientTypeDescription FROM tblClientTypes ORDER BY ClientTypeDescription;
For column widths I used "0cm;3cm"
Step 2. Create a query that links together the main client table and the lookup table for the client types. In my case the query looked like this:
SELECT tblClients.ClientCode, tblClients.ClientName, tblClientTypes.ClientType, tblClientTypes.ClientTypeDescription FROM tblClientTypes INNER JOIN tblClients ON tblClientTypes.ClientType = tblClients.ClientType WHERE (((tblClientTypes.ClientType)=[forms]![frmClientsByType]![cboClientType])) ORDER BY tblClients.ClientName;
Notice how I get the query to use the value chosen from the combo box on the form as criteria. Regular readers will know that I love this technique.
Step 3. Create a report that uses the query as its record source.
Step 4. Return to the form and add a command button that opens the report.
Step 5. Test the form
NOTE: up to this point we have NOT used any VBA. You do not need to be a programmer to use some neat looking methods.
Next: how do we add the word ALL to the list?
One method is to use the keyword UNION in the query. Carefully examine the new Row Source for the combo box. I have made the new portion bold.
SELECT ClientType, ClientTypeDescription FROM tblClientTypes UNION SELECT "(ALL)" as ClientType, "(All Client Types)" as ClientTypeDescription FROM tblClientTypes ORDER BY ClientTypeDescription;
The union part mirrors the original select fields portion, but puts in "hard-coded" values. By surronding the new item with parentheses, I have ensured it will sort to the top of the list, if all other entries are normal alphabetic ones.
Now, when the end user clicks the drop down on the combo box, they will see all the regular codes PLUS the new "ALL" choice.
Now that the combo box works with the new entry, how do we get the report to notice and pick up all the records, rather than just one code? I will present one possible solution. There are others. Mine requires a simple change to the query. Let's just dive right in, and examine the query modification.
SELECT tblClients.ClientCode, tblClients.ClientName, tblClientTypes.ClientType, tblClientTypes.ClientTypeDescription FROM tblClientTypes INNER JOIN tblClients ON tblClientTypes.ClientType = tblClients.ClientType WHERE (((tblClientTypes.ClientType)=[forms]![frmClientsByType]![cboClientType])) OR ((("(ALL)")=[forms]![frmClientsByType]![cboClientType])) ORDER BY tblClients.ClientName;
Rather than putting a field in the query grid (on the field row) I have typed the string constant "(ALL)", and placed the same criteria string already in use on the next "or:" row.
Test, and if all is okay, you are done. And, look ma, no VBA!!!
If you would like the sample database that I created to test this (Access 2000 version only) please visit this page.