Excel VBA - Create Treeview from recordset

Question

Hi Guys and Girls,

I have a SQL 05 Stored Procedure that brings back around 180 rows with the structure:

ID | Name | ParentId.

What I would like to do is create a Treeview based on the records returned. Below is a sample I adapted from elsewhere on the forum (here)

I nearly have it doing what I want but not quite.

This is the problem I have, The root is added ok. The next layer down is added ok. However, it does not know what the next ParentId should be. Should I just incremement a variable and try and find matches? I guess I should include that in the top method.

Hopefully I am not far off and need a shove in the right direction,

Many Thanks in advance Michael

As a final thing, the BOF AND EOF checks are there in the loop as I seem to have had a lot of errors being thrown if I don't have it.

Private Sub MakeTree(ByVal rs As ADODB.Recordset)
    rs.MoveFirst
    Do Until rs.EOF
        If (IsNull(rs.Fields("ParentID"))) Then
            Call TVFunds.Nodes.Add(, , "Key" + CStr(rs.Fields("Id")), rs.Fields("Name"))
            Call MsgBox("Key" + CStr(rs.Fields("Id")) + " " + rs.Fields("Name"), vbInformation, "Added Root")
        Else
            DrawNode rs, rs.Fields("ParentID"), rs.Fields("ID")
        End If
        If rs.BOF <> True And rs.EOF <> True Then
            rs.MoveNext
        End If
    Loop
End Sub

    Private Sub DrawNode(ByRef r As ADODB.Recordset, ByRef pId As Integer, ByRef Id As Integer)
   r.MoveFirst
   Do Until r.EOF
       If (r.Fields("ParentId") = pId And r.Fields("Id") = Id) Then
            Call TVFunds.Nodes.Add("Key" + CStr(r.Fields("ParentId")), tvwChild, "Key" + CStr(r.Fields("Id")), r.Fields("Name"))
            Call MsgBox("Key" + CStr(r.Fields("ParentId")) + " Key" + CStr(r.Fields("Id")) + " " + r.Fields("Name"), vbInformation, "Added")
            Id = Id + 1
            DrawNode r, pId, Id
        End If
        If r.BOF <> True And r.EOF <> True Then
            r.MoveNext
        End If
    Loop
End Sub

Answer 1

First things first: Do Until... loops always execute at least once. So, if the recordset has no rows, it will still enter the loop. That's why you were getting those errors without the EOF/BOF checks.

Change those Do Until... loops to Do While... loops like this:

Do While Not rs.EOF
        If (IsNull(rs.Fields("ParentID"))) Then
            Call TVFunds.Nodes.Add(, , "Key" + CStr(rs.Fields("Id")), rs.Fields("Name"))
            Call MsgBox("Key" + CStr(rs.Fields("Id")) + " " + rs.Fields("Name"), vbInformation, "Added Root")
        Else
            DrawNode rs, rs.Fields("ParentID"), rs.Fields("ID")
        End If

        rs.MoveNext
Loop

Answer 2

Hi all,

Problem solved :) Thanks Chris for the start! Will post code tonight incase anyone else has issues!

Cheers, Michael