Let's take this technique to the next obvious step.

(article continues after sponsor spot)

The methods outlined in part 1 and part 2 of this series only allow a single choice. You can use a wild card symbol on text values, but you are still restricted to one choice. What if we want to allow 2 or more distinct choices, like multiple clients in a single report.

This article describes 3 techniques that I know of within Microsoft® Access.

Method A - use the technique from the two lists article to choose which clients you want printed. Use a special flag in the table to track which ones, and the query that feeds the report can use the flag as a criteria.

Method B - use a multi-select listbox and take all the selected values and store them in a temporary table. This table links to the other tables, thus acting as a filter.

Method C - similar to method B except no table is required. A special technique is used to pass multiple choices as criteria to the query.

Here is some of the special VBA code I used.

For method A refer to the article mentioned above.

For method B and C the method of reading through the multi-select listbox is shown.

The key code lines are
    For Each varItem In lstChosen.ItemsSelected
and
    rst!ClientNumber = lstChosen.ItemData(varItem)

These are critical for reading through all the selected items from the listbox.

The only challenge comes in method C. I use the following technique to built a list of items for the criteria.

An example of the output created is "3,6,14". However, the query will not accept this created string as criteria, even if you change the commas to " or ", as in "3 or 6 or 14".

The solution came courtesy of Microsoft's Knowledge Base, article # 210530.

The MS Access sample database (Access 2000 version) that I used to test these techniques is available by filling in the following form, and clicking the Submit Request button. This request will result in an email being sent to you giving the current download site. The download is only supplied in Zip format (74 KB), so you will need WinZip or similar to open it.

This Microsoft® Access sample database has 2 tables, 3 queries, 4 forms, and 3 reports. Just open the menu form and buttons take you the rest of the way. Then examine the designs, including the commented VBA code. Request the sample here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy