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
– Parfait, Commented Oct 7, 2015 at 17:28
I am doing this within Excel. Adding population code to edit — Jeff Beagley
– Jeff Beagley, Commented Oct 7, 2015 at 17:32
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
– Tim Williams, Commented Oct 7, 2015 at 18:41
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
– Jeff Beagley, Commented Oct 7, 2015 at 18:56

codersl · Accepted Answer · 2015-10-08 00:41:59Z

1

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

answered Oct 8, 2015 at 0:41

codersl

2,3425 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Beagley Over a year ago

I'm receiving object required on this line - For i = 0 To form_mode.ListCount - 1 I originally had it declared as a value at the top of my sub, so I removed it.. no luck, then added it back in as form_mode = Main_Window.form_mode.List

codersl Over a year ago

Can you try Me.form_mode.ListCount? And make sure it's in the same section as the User_Initialize function.

Collectives™ on Stack Overflow

VBA - Listbox and SQL IN Statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related