Multiple Selection wth List Box / Combo Box

[rkalapura]

Hai,

I have a form with a Combobox in Access 2010. It is bound to a table and has ten items in the list. These are the list of different departments which when I select, gives me the Report of that dept. I would like to select multiple items from the list, so that I can combine those dept reports whenever necessary.

Is it possible to select multiple values from a combo box or list box? I was searching for the Allow multiple value select property in the Property sheet, but couldn't find.



I really appreciate your valuable help.

Thank you

[pbaldy]

Here's one way

http://www.baldyweb.com/multiselect.htm

The property for a listbox is on the Other tab.

[rkalapura]

Thanks Pbaldy for your prompt reply. Is this code go to the afterupdate event of the listbox?

Secondly I tried to use the Listbox's Multi value property. It allows me to select multiple items if I choose "Simple" or "Extended" from its drop-down, but won't pass the value to the "Where condition". If I use "None", it won't allow to choose multiple items, but pass value to the Where condition. Is there any way I can choose multiple item and pass the values to the reports' "Where condition"? Or any chance to use a combobox with some checkboxes instead?

Thank you again for your help.

[Missinglinq]

The code could be triggered through the AfterUpdate event, on leaving the Listbox, or through a Command Button. With Multi-Select set to Simple or Extended, the Listbox no longer has a Value Property. You have to retrieve the selected items using the code that Paul's example shows, specifically

'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'," 
Next varItem

After the code runs, the Variable strWhere holds the selected values you need.

The 'Combobox with Checkboxes' requires using one of the new Multi-valued Fields in 2010. This is something that most experienced developers stay away from, and really should be avoided, I would think, by newbies.

Linq ;0)>

[rkalapura]

Thanks for your help again. I tried the code and it gives me an error like this:

Runtime Error 3075

Syntax Error. Missing operator in Querry Expression, 'DeptID In('O17','O19','O20')'

Note: DeptID is a Text Field and I select O17 and O19 to test the code from the Listbox

Code is running the click event of a command button.
Thank you for your help again.
Regards

[pbaldy]

I don't see a problem with that right off. Can you post the full code or the db itself?

[rkalapura]

Dear Paul,

The following code is in the OnClick event of a command Button. Hope I have not missed anything in this.

Private Sub Command9_Click()
Dim strWhere As String
Dim Ctl As Control
Dim Varitem As Variant
If Me.Machine.ItemsSelected.Count = 0 Then
MsgBox "Must Select atleast One Machine"
Exit Sub
End If
Set Ctl = Me.Machine
For Each Varitem In Ctl.ItemsSelected
strWhere = strWhere & "'" & Ctl.ItemData(Varitem) & "',"
Next Varitem
strWhere = Left(strWhere, Len(strWhere) - 1)
DoCmd.OpenReport "machine log", acViewReport, , "Machine No In (" & strWhere & ")", acWindowNormal
End Sub


Thanks for your help

[pbaldy]

That appears to be for something else, but the inadvisable space would require the field name to be bracketed.

[rkalapura]

Hai Paul,
As suggested by you, I put the filed name in bracket. Now it is not showing any Runtime Error, but it is not picking the selected value from the list box. I get an empty report always even I select one or more items from the list.
This is just for your info.

Thanks

[pbaldy]

You can use this to see hat the final string looks like

http://www.baldyweb.com/ImmediateWindow.htm

[pellissier]

Here's one way

http://www.baldyweb.com/multiselect.htm

The property for a listbox is on the Other tab.

I need to print a report using a form with a multiple listbox from which users choose departments. I used your excellent code and got that part working. I now need to add the option to sort those departments by one of 3 fields on the report. I put an option group on my criteria form but can't figure out how to add that criteria to your excellent code on the print button.

I also need to unselect all selections as sometimes the user will need to print all the departments.

Thank you for your help -

[pbaldy]

Answered on your duplicate post:

https://www.accessforums.net/reports...ort-32359.html