VBA - Listbox and SQL IN Statement

Question

I have a listbox that is populated with the following data. I only want to be able to select ( visually ) by the name column.

id | name
1  | value1
2  | value2

I need a few conditions to happen and apply them in a where statement in my SQL.

If nothing is selected, then just omit the where statement all together
If 1 value is included, place the where id = 1
If both values are selected, then create an IN statement like the following

IN ('1','2')

EDIT --//

This is the code that populates the listbox upon initialization

Sub UserForm_Initialize()

    'SQL query that will populate the Mode Box
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    cnn.Open "Provider=SQLOLEDB;Data Source=yadayda;" & _
             "Initial Catalog=db;" & _
             "Integrated Security=SSPI;"

    Set rst = cnn.Execute("SELECT [ID] AS [ID],[Name] AS [Name] FROM [Table]")
    rst.MoveFirst
    i = 0

    With Me.form_mode

        .Clear
        Do
            .AddItem
            .List(i, 0) = rst![Mode]
            .List(i, 1) = rst![ID]
            i = i + 1
            rst.MoveNext
        Loop Until rst.EOF

    End With

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

EDIT -// Got it to work using the following

`Dim strFilter As String Dim i As Integer

    For i = 0 To Main_Window.form_mode.ListCount - 1
        If Main_Window.form_mode.Selected(i) Then
            If strFilter <> vbNullString Then strFilter = strFilter & ", "
            strFilter = strFilter & "'"
            strFilter = strFilter & Main_Window.form_mode.List(i, 1)
            strFilter = strFilter & "'"
        End If
    Next i`

Thanks to @ralph in this thread - VBA - SQL with optional joins

Your tag uses excel-vba but this sounds like an Access form listbox with an SQL rowsource. — Parfait
I am doing this within Excel. Adding population code to edit — Jeff Beagley
What specifically is the issue with creating the "where" clause? Note you can simplify your logic by also using IN() for the single selection case. — Tim Williams
Ah I was under the impression you couldn't do a WHERE [col] IN ('1'). I need to be able to select multiple values, say I have 10 options to choose from but only select 5. How to get that to loop the values in an IN statement. — Jeff Beagley

codersl codersl · Accepted Answer · 2015-10-08T00:41:59

In your UserForm_Initialize function set Me.form_mode.MultiSelect = fmMultiSelectMulti or fmMultiSelectExtended to allow multi-selects (or set via ListBox properties).

Then in a button click event use the following code to get the selected values:

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim arr() As String
    For i = 0 To form_mode.ListCount - 1
        If form_mode.Selected(i) Then
            ReDim Preserve arr(i)
            arr(i) = form_mode.List(i, 1)
        End If
    Next i
    MsgBox "WHERE [ID] IN ('" & Join(arr, "','") & "')"
End Sub

VBA - Listbox and SQL IN Statement

1 Answers