Parameters in a form

[Payal Patel]

If I have a query and I have a form linked to it, when I run the query and it asks me for the parameter, is there a way I can list more than one for example, branch at a time branch at a time? (the "enter Parameter Value" box)

Thanks,
P.

 

[CraigDolphin]

The way you tyically handle more than one parameter criterion in a single field is to use the In operator.

Eg.

In(1,2,3)

would limit results to rows containing either a 1, a 2, or a 3 in that field.

If you wish to use a form control to achieve that effect then I suspect you're either out of luck or needing to use some vba code in combination with a multi-select listbox control to make it work for you. Unfortunately, controls on forms only return one value to queries. Even a multi-select listbox does not return several values to the query without a lot of help from vba.

If you need to achieve this effect, you need to use vba to loop through the rows in the list box and to build a suitable criteria string as it encounters selected rows. Then you can provide this string to the query. You could do this by using vba to re-write the sql of the query to include the new criteria string. You can search this forum for examples of code that does this kind of thing. Alternatively (and probably less recommended) you could save the criteria string value to a global variable that the query uses as it's criteria parameter.

The third option is not to use a form for your parameter at all. Just type in the list of values separated by commas when the prompt appears for the unsourced parameter that is in your criteria expression.
e.g.

In ([Type list of criteia separated by commas])

 

[highandwild]

Have a look at the customer form in the posted database.

Either have the form requery when a customer is selected or have a Requery button.

The selection form could be a popup and could have lots of other functionality including a search facility, sort facility, view more details option etc etc.

Good thing about this is that the selected customer list can be used for a report, emailing customers, saved for later use, deleted etc etc.

Only one idea but it uses limited code which is good and easy to maintain.

Let me know what you think?