How to choose two items from a dropdown list & search

[SNicholls]

In a database that contains a history of previous company projects, the data entry form includes a dropdown list of corporate offices from a lookup table. Users may query by office. (Users aren't familiar with Access; I provide command buttons to query [which office?] and produce the results in reports.)

In the data entry form, some users want to choose more than one office for a given project; the only thing I can think of is two fields: primary office, secondary office. Is there a better way?

If there are two fields (primary office, secondary office), how could I make it simple for the user to search both fields at once?

SNicholls

 

[Presterman]

An idea:
Have a list box where users can make multiple choices. This is not possible with a combo box (as far as I can remember). This is the best solution to this problem, I think.

 

[FancyPrairie]

I would setup a label that says "All Offices" and under the label I would have a multi-select list box that contains the names of all of the Offices. When the user clicks on the label "All Offices" I would programmatically unselect all items selected in the list box. When the user selected an item in the list box I would unselect (change the background color of the label) the label. It would look something like this:

-----------------------
| All Offices |
+---------------------+
| Office 1 |
| Office 2 |
| ... |
+---------------------+
(0 items selected)

The reason I use a label to select All Offices is so that the code I use to build the Where clause will not have to deal with ALL offices. By default, if no items are selected in the list box, then no Where clause is created and, therefore, all offices are returned.

So...check out this FAQ faq181-5497 for a function that builds the Where clause for you. If you create a library database (not required) and place the code in the library and reference the library to your database, then future database that you create can reuse the code.

You only have to do 3 things to use the code in the FAQ.
1. Create a new module and copy the code from the FAQ and place it into the new module.
2. Set the tag propery of your list box as described in the FAQ.
3. Open your report as specified in the FAQ

This code is overkill for your simple problem, but I think you will find it very useful when you build other databases. If works for single and multi-select listboxes, combo boxes, text boxes, dates, ranges, option groups and check boxes. The code may look daunting, but you don't need to worry about understanding it, just use it.

 

[SNicholls]

I need more basic help, please. My Access Bible 2003 says that to use multiple selections I need to "define a program to capture the selection." How do I do that?

And then will I be able to query that field in the usual way?

Thank you.

SNicholls

 

[FancyPrairie]

That's what the function BuildWhere (found in the FAQ) does. It loops thru all of your controls on your form. If it finds a control whose Tag property is set as described in the FAQ (i.e. Where=...) it builds the Where clause. In your case, it will determine which items were selected in the list box and return the Where clause for you. For example, suppose the user selected Office2, Office7, and Office99 from the list box. The BuildWhere function would return something like this:

(strYourOfficeID In (office2, office3, office99))

To test it, do the first 2 steps I mentioned in the previous post. Then place a command button on your form and via the OnClick event, do this:

MsgBox BuildWhere(Me)