CODEWITHSUNDEEP
vbaexceluserform
James
James

Reputation: 187

Userform Listbox populate and clear depending on selection from another listbox

I have 2 listboxes, lbA and lbB. I am populating lbA using SQL script which works fine. Now what i would like to do is when i select an item from lbA, lbB gets populated again from a SQL script. It works when selecting only one item from lbA but selecting two or more or unseleting an item then it either adds repeated items or doesnt delete items from lbB. I am puzzled as to how to approach this. Can anyone please help?

This is the code so far:

Private Sub lbA_Change() 
    Dim Num As Integer 
    Dim lngIndex As Long 

    If Me.lbA.ListIndex <> -1 Then 
        For lngIndex = 0 To lbA.ListCount - 1 
            If lbA.Selected(lngIndex) Then 


                Dim cnPubs As ADODB.Connection 
                Set cnPubs = New ADODB.Connection 
                Dim strConn As String 
                strConn = "PROVIDER=SQLOLEDB;" 
                strConn = strConn & "DATA SOURCE=MSSQLSERVER2008;INITIAL CATALOG=MyDB;" 
                strConn = strConn & " INTEGRATED SECURITY=sspi;" 
                cnPubs.Open strConn 
                Dim rsPubs As ADODB.Recordset 
                Set rsPubs = New ADODB.Recordset 
                With rsPubs 
                    .ActiveConnection = cnPubs 
                    .Open "My SQl Statement" 
                End With 
                If rsPubs.EOF Or rsPubs.BOF Then 
                    Exit Sub 
                End If 
                rsPubs.MoveFirst 
                With Me.lbB 
                    .Clear 
                    Do 
                        .AddItem rsPubs![strName] 
                        rsPubs.MoveNext 
                    Loop Until rsPubs.EOF 
                End With 
                rsPubs.Close 

            End If 
        Next 
    End If 
End Sub

Upvotes: 0

Views: 1258

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Try a function like this to get the SQL statement.

Function GetSQLFromListbox(lbx As MSForms.ListBox)

    Dim i As Long
    Dim sIn As String

    If lbx.ListIndex <> -1 Then
        sIn = " WHERE field IN("
        For i = 0 To lbx.ListCount - 1
            If lbx.Selected(i) Then
                sIn = sIn & "'" & lbx.List(i) & "',"
            End If
        Next i
        sIn = Left$(sIn, Len(sIn) - 1) & ")"
    End If

    GetSQLFromListbox = "SELECT * FROM table" & sIn

End Function

It should return something like

SELECT * FROM table WHERE field IN('2''3')

Then create a recordset from that statement and loop through it to fill the second listbox. Note that if nothing is selected, it returns the SQL without a WHERE clause, so you'd get everything. You might need to adjust that to suit your situation.

Upvotes: 1

Related Questions