Passing Combo box selection to Query

[MorningSun]

I have a form that has a combo box on it (cboProduct). I need users to be able to select multiple products but I know that combo boxes don't allow this. I've made the combobox into 2 columns. One for the product (or group of products which just acts as the visual label) and the other for the criteria I'd like to pass to the query (which is the bound column). So, the combo box is like this:
Column1(display)-----Column2(bound)
Product1-------------Product1
Product2-------------Product2
Product3-------------Product3
Product1/Product2----Product1 or Product2
Product1/Product3----Product1 or Product3

In the Product field of the query I have the criteria:
Like[forms]![frmselectReport]![cboProduct])

I would think this would work because when I manually type the criteria in the query without referencing the combo box I type this:

Like Product1 or Product2

And it will work. The query itself changes it to Like "Product1" or "Product2" but I've even tried adding the quotation marks in my combobox too to no avail. The query and combobox work fine with the single products so I know it's set up right and reading right but I can't figure out the exact syntax to pass to the query to make it qry the multiple products selections...

I hope I explained that okay!! Any advice?
Thank you so much!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[TheAceMan1]

How are ya MorningSun . . .

[blue] . . . I need users to be able to select multiple products [red]but I know that combo boxes don't allow this.[/red][/blue]

Since you've admitted [blue]you can't make multiple selections from a combobox[/blue], [red]why are you attempting to do so? [/red]

So . . . whats wrong with a [blue]listbox[/blue] where multiple selections can be made? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MorningSun]

Because that's what I need to do and I don't like listboxes because they don't "drop down" but rather have to be expanded at all times... unless there's something I don't know....

I did even try a listbox that allowed multiple selections (I left the name cboProduct and had the qry criteria still set to Like forms!frmSelectReport!cboProduct) but when I ran the query I didn't get any results even when only a single product was selected...much less two... so I'm missing something there too!!

I also just don't understand why a combobox will pass "Product1" to the query criteria and work but won't pass "Product1" and "Product2" to the query when that is exactly what you can manually type into the criteria of the query and get it to work (Like "Product1" and "Product2" gives me what I want, why can't it read that exact text from the combobox?). Seems like there must be a way but if not lets move on to listboxes!! =)

Is there some different way to reference a listbox that's different than a combo box?

Thank you so much for your prompt reply!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[MorningSun]

Ok, this works:

I had to put the reference to the combobox on multiple criteria lines in the query...

Criteria line 1 for Product: IIf([forms]![frmselectReport]![cboProduct]="Product1/Product2/Product3","Product1",[forms]![frmselectReport]![cboProduct])

Criteria line 2 for Product: IIf([forms]![frmselectReport]![cboProduct]="Product1/Product2/Product3","Product2",[forms]![frmselectReport]![cboProduct])

Criteria line 3 for Product: IIf([forms]![frmselectReport]![cboProduct]="Product1/Product2/Product3","Product3",[forms]![frmselectReport]![cboProduct])

This will pick up the single products as well as multiple products if it is indicated in the dropdown item selected.




PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.