0

I am trying to create three cascading comboboxes (the third dependent from the second, the second from the first). I have found a way to create and populate the first two, but I am struggling with the third one. Note that I want to populate them starting from just three columns with headers One, Two, Three.

I have used the method listed here https://www.encodedna.com/excel/how-to-create-cascading-combo-box-in-excel-using-vba.htm which worked fine for the first two. I have tried to write an additional Sub for the third combobox similar to the second but it did not work

Private Sub cmbDue_Change()
    ' THE CHANGE EVENT OF THE SECOND COMBO TO POPULATE THE THIRD COMBO BOX.

    If Trim(cmbDue.Text) <> "" Then

        SetConn     ' SET THE CONNECTION TO THE DATABASE.

        ' SQL QUERY TO FETCH VALUE IN THIRD COLUMN BASED ON THE SELECTED CATEGORY.
        sQuery = "SELECT Three FROM [First$] WHERE " & _
            "Two = '" & cmbDue.Text & "' " & _
            "ORDER BY Three"

        cmbTre.Clear          ' CLEAR THE THIRD COMBOBOX.


        If rs.State = adStateOpen Then
            rs.Close
        End If

        rs.CursorLocation = adUseClient

        ' POPULATE CASCADING COMBO BOX WITH VALUES.
        rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            Do While Not rs.EOF
                cmbTre.AddItem rs.Fields(0).Value
                rs.MoveNext
            Loop
        End If
    End If
End Sub

This line does not work:

    rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic

...while it worked fine in the first sub to populate the second combobox. I do not get a specific error message, I am just asked to debug.

My understanding is that rs.open should open and ADODB recordset consisting of my original table containing the data, which was created with this sub

Option Explicit

Public myConn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public sQuery As String

' SET A CONNECTION.
Sub SetConn()
    If myConn.State = adStateOpen Then
        myConn.Close
    End If

    Dim sConnString As String
    sConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    myConn.ConnectionString = sConnString
    myConn.Open         ' OPEN THE CONNECTION.
End Sub

"sQuery" should run the SQL query set in the previous sub, to be run on my table "rs"(the ADODB recordset), while "myConn" should simply connect to the recordset.

But it does not work.

Paolo
  • 1
  • 1
  • 3
  • 1
    "This line does not seem to work" - in what way does it not work? Error message/nothing happens? – Tim Williams May 22 '19 at 22:50
  • I am asked to debug it – Paolo May 23 '19 at 06:34
  • Actually the question is what error do you get. If there is no error explain what your code does vs what you expect it to do. You need to be much more specific about **what exactly** is going wrong and ask a proper question to that. You might benefit from reading [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) • We need precise questions to answer, questions like *"How can I do X/Y?"* are in most cases too broad to answer (see [ask]). – Pᴇʜ May 23 '19 at 06:37
  • @PEH I have made some edits. It remains difficult as I do not get a specific error message. – Paolo May 23 '19 at 07:32
  • If `Three` is a date-type then you probably need `#` around the query value. https://stackoverflow.com/questions/31502464/excel-adodb-query-dates – Tim Williams May 23 '19 at 16:08

0 Answers0