How to use unbound multi-select combo box to filter a query for report

[mshakeelattari]

I have a form with an unbound combobox. This combobox serves as the criteria for a Query that then feeds a Report. It is simple, efficient, and works very well. I am also using a vba codefor this purpose.

I would like for this combobox to allow for multiple selections so that when the report is produced more than one selection would show on the report, instead of running the report two different times. How is it possible in MS Access?

Any help will be appreciated.

[jimatqsi]

mshakeelattari,
A combobox does not allow for multiple selections. If you use a list box instead you will see in the Other tab of Properties an option for Multi Select.

You can loop through the list of items to test the .Selected property to identify which items in the list have been selected. Something like this:

dim i as integer
for i = 0 to me.listname.listcount-1
if listname.Selected(i) then
do stuff
end if
next

Jim

[NeoPa]

Hi.

I agree with Jim. There is no capability in ComboBoxes to support multiple selections, but there is in a ListBox where .MultiSelect is True. Repeating Jim again, the Collection within a ListBox is called .Selected. I'm not sure (Jim can correct me if I'm off here.) the logic of getting i from .ListCount of the ListBox then using it on ListBox.Selecte d works very reliably, but heres some alternative code that should work for you :

Dim varItem As Variant

For Each varItem In Me.YourListBox.Selected
    Debug.Print varItem
Next varItem

[jimatqsi]

You may well like Neopa's solution better than my own. I've used both methods but I often forget about that .Selected collection.

I use listboxes extensively, nearly every form I create has at least one listbox on it. They are so very useful. But I don't often use multi-select lists. I use two lists instead, so the user can clearly see what has been selected, no matter how long the origin list is.

I'm glad to be reminded about .Selected.

[NeoPa]

Hi Jim.

Now you have me wondering. Was your line #3 supposed to be different? Perhaps :

If Me.ListName(i).Selected Then ...

I say this as your code already included the Selected property there. I just can't see that it could work that way. Clearly I need to refresh my understanding of exactly what it contains.

The documentation indicates it actually returns Longs. I need to look into this further to understand properly what information it actually provides.

[jimatqsi]

Now you have me looking at it more closely. I understand it to be returning a true/false value. Been using it for years ... or have I? I don't use multi-select often enough to have a complete grasp of the details ready at hand.

I poked around the Internet and found this example from Microsoft. Ade went off kilter a little bit. The collection he refers to is actually .ItemsSelected. Here's MS' example, which does not reference the ItemsSelected property:

Private Sub cmdCopyItem_Click() 
 CopySelected Me 
End Sub 
 
Public Sub CopySelected(ByRef frm As Form) 
 
 Dim ctlSource As Control 
 Dim ctlDest As Control 
 Dim strItems As String 
 Dim intCurrentRow As Integer 
 
 Set ctlSource = frm!lstSource 
 Set ctlDest = frm!lstDestination 
 
 For intCurrentRow = 0 To ctlSource.ListCount - 1 
 If ctlSource.Selected(intCurrentRow) Then 
 strItems = strItems & ctlSource.Column(0, _ 
 intCurrentRow) & ";" 
 End If 
 Next intCurrentRow 
 
 ' Reset destination control's RowSource property. 
 ctlDest.RowSource = "" 
 ctlDest.RowSource = strItems 
 
 Set ctlSource = Nothing 
 Set ctlDest = Nothing 
 
End Sub

And here is an example using the collection:

Sub BoundData() 
 Dim frm As Form, ctl As Control 
 Dim varItm As Variant 
 
 Set frm = Forms!Contacts 
 Set ctl = frm!Names 
 For Each varItm In ctl.ItemsSelected 
 Debug.Print ctl.ItemData(varItm) 
 Next varItm 
End Sub

I'm so glad to revisit this because it may help me in the near future. I should take advantage of multi-select more often.

Happy coding!

[MrXmas]

I have a reusable dialog box that I feed a list of values to for the user to make their selections.
With the dialog box, I can allow the user to select if they want to Include or Exclude the selections in the list box, if they want to also include Null values, etc. When the user clicks on a button to filter by (for instance) Job Status, the reusable list box opens up and displays their current filter. They can edit that filter, adding or removing items, then the comma delimited list is returned in code. I store the CSV list and decode it to the button so that the user can always see what their current filter is.

A simpler way is to just put a list box on screen with MultiSelect set to true. Then set the Click event for the listbox to gather up a list of everything that's checked with a routine like this:

Public Function ListBoxItems(ctl As Control, Optional blnForceText As Boolean) As String

Dim vItem As Variant
Dim sList As String
Dim blnTextField As Boolean

    blnTextField = IIf(IsMissing(blnForceText), False, CBool(Nz(blnForceText, 0)))
    For Each vItem In ctl.ItemsSelected
        If blnTextField Or Not (IsNumeric(ctl.Column(0, vItem))) Then
            sList = sList & "'" & ctl.Column(0, vItem) & "', "
        Else
            sList = sList & ctl.Column(0, vItem) & ", "
        End If
    Next vItem
    
    If Len(sList) > 0 Then sList = Left(sList, Len(sList) - 2)
    ListBoxItems = sList
End Function

Rebuild your underlying query with the list above in a WHERE clause.