VBA to Populate Excel Userform Treeview from MS SQL

B

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
I can not seem to figure this out, can someone assist please.

I have a userfrom in excel that has a treeview, I am trying to populate the treeview with a recordset from ms sql using ADO.

I have managed to populate the Parent Nodes but have not been able to figure out how to populate the child nodes. Everything with the connection to the database and getting a recordset returned seems to be working as I do get the parent nodes to populate.

I am a rookie in vba and have been able to eventually figure things out with google and youtube, but I am stuck on this one...cant find much online related to what I am trying to do.

I have tried the following code and I get a error " Error number = 2147257114 Invalid object"

This is the first time I have tried using treeview.

All assistance would be greatly appreciated.

VBA Code: 
    Me.TreeView1.Nodes.Clear
    
    'load Treeview
    Do While Not rs.EOF
    
        Me.TreeView1.Nodes.Add = rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add rs.Fields.Item("BidItemNo"), tvwChild, rs.Fields.Item("BidItemDescription"), rs.Fields.Item("BidItemDescription")

        
    rs.MoveNext
    Loop
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
The first node needs to have its key set, which is the third parameter. The fourth is the node text. You have the child node correct, but it doesn't have a parent it can reference because the parent key wasn't set. You can ignore the first parameter for the parent node and set its Relationship to tvwNext as insurance (it might be default if left blank but I haven't looked it up).
VBA Code: 
        Me.TreeView1.Nodes.Add , tvwNext, rs.Fields.Item("BidItemNo"), rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add rs.Fields.Item("BidItemNo"), tvwChild, rs.Fields.Item("BidItemDescription"), rs.Fields.Item("BidItemDescription")
 
Upvote 0
shknbk2 said:
The first node needs to have its key set, which is the third parameter. The fourth is the node text. You have the child node correct, but it doesn't have a parent it can reference because the parent key wasn't set. You can ignore the first parameter for the parent node and set its Relationship to tvwNext as insurance (it might be default if left blank but I haven't looked it up).
VBA Code: 
        Me.TreeView1.Nodes.Add , tvwNext, rs.Fields.Item("BidItemNo"), rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add rs.Fields.Item("BidItemNo"), tvwChild, rs.Fields.Item("BidItemDescription"), rs.Fields.Item("BidItemDescription")
Click to expand...
It is now giving me a Invalid Key Error.

Any suggestions? Can I combine a couple fields from the recordset to make a different key or something?
 
Upvote 0
Try this:
VBA Code: 
        Set n = Me.TreeView1.Nodes.Add(, tvwNext, , rs.Fields.Item("BidItemNo"))
        n.Nodes.Add , , , rs.Fields.Item("BidItemDescription")
 
Upvote 0
Or this:
VBA Code: 
    Me.TreeView1.Nodes.Clear
    i = 1
    'load Treeview
    Do While Not rs.EOF
        Me.TreeView1.Nodes.Add , tvwNext, "n" & i, rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add "n" & i, tvwChild, , rs.Fields.Item("BidItemDescription")
        i = i + 1
        rs.MoveNext
    Loop
 
Upvote 0
shknbk2 said:
Or this:
VBA Code: 
    Me.TreeView1.Nodes.Clear
    i = 1
    'load Treeview
    Do While Not rs.EOF
        Me.TreeView1.Nodes.Add , tvwNext, "n" & i, rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add "n" & i, tvwChild, , rs.Fields.Item("BidItemDescription")
        i = i + 1
        rs.MoveNext
    Loop
Click to expand...
I will try this out.

One thing, is the first line of this code the Root?

The Parent is “BidItemNo”
Children of BidItemNo is “BidItemDescription”

Maybe I need to have a root?
 
Upvote 0
shknbk2 said:
Or this:
VBA Code: 
    Me.TreeView1.Nodes.Clear
    i = 1
    'load Treeview
    Do While Not rs.EOF
        Me.TreeView1.Nodes.Add , tvwNext, "n" & i, rs.Fields.Item("BidItemNo")
        Me.TreeView1.Nodes.Add "n" & i, tvwChild, , rs.Fields.Item("BidItemDescription")
        i = i + 1
        rs.MoveNext
    Loop
Click to expand...

It is close but not quite. Any suggestions. The fields are a little different as I had to make some changes in the database.

The code that gives me the result as in the attached picture is below.
In the attached picture Parent "10-Concrete Curb" should only be 1 node of the whole tree and have all of the displayed children under it...if this makes sense.
the Parent text is: rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription")
the Child text is: rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")

I think I should probably have the Root for the whole tree, the Root is Me.txtContractTitle.value

Im not sure if I mentioned it...There can be many Parent Nodes and each of the Parent Nodes can have multiple childs. I will also need to go at least on more level.
So the childs can also have multiple nodes.

VBA Code: 
    Do While Not rs.EOF
        Me.TreeView1.Nodes.Add , tvwNext, "n" & i, rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription")
        Me.TreeView1.Nodes.Add "n" & i, tvwChild, , rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")

        i = i + 1
        rs.MoveNext
    Loop

Treeview .png
 
Upvote 0
I think I'm getting closer to understanding what you need. For testing, I made the following dataset:
BidItemNoBidItemDescriptionActivityCodeActivityDescription
10Concrete Curb1510Description for 1510
10Concrete Curb1511Description for 1511
10Concrete Curb1512Description for 1512
10Concrete Curb1513Description for 1513
10Concrete Curb1514Description for 1514
10Concrete Curb1515Description for 1515
20Concrete Curb and Gutter1516Description for 1516
20Concrete Curb and Gutter1517Description for 1517
20Concrete Curb and Gutter1518Description for 1518
20Concrete Curb and Gutter1519Description for 1519
20Concrete Curb and Gutter1520Description for 1520
30Concrete Sidewalk1521Description for 1521
30Concrete Sidewalk1522Description for 1522
30Concrete Sidewalk1523Description for 1523
30Concrete Sidewalk1524Description for 1524
30Concrete Sidewalk1525Description for 1525
30Concrete Sidewalk1526Description for 1526
40Sidewalk Trip Ledge Cutting1527Description for 1527
40Sidewalk Trip Ledge Cutting1528Description for 1528
40Sidewalk Trip Ledge Cutting1529Description for 1529
40Sidewalk Trip Ledge Cutting1530Description for 1530
40Sidewalk Trip Ledge Cutting1531Description for 1531


I made this code to test the idea:
VBA Code: 
Private Sub UserForm_Initialize()
    Dim c As Range
    Dim n As Node
   
    Me.TreeView1.Nodes.Clear
    Me.TreeView1.Nodes.Add , , "root1", "Contract Title"
    For Each c In Range("A2:A23")
        Set n = GetNode(c.Value)
        If n Is Nothing Then
            Set n = Me.TreeView1.Nodes.Add("root1", tvwChild, "n" & c.Value, c.Value & " - " & c.Offset(0, 1).Value)
        End If
        Me.TreeView1.Nodes.Add "n" & c.Value, tvwChild, "n" & c.Offset(0, 2), c.Offset(0, 2) & " : " & c.Offset(0, 3)
    Next c
    For Each n In Me.TreeView1.Nodes
        n.Expanded = True
    Next n
End Sub

Function GetNode(nText As String) As Node
    Dim n As Node
    For Each n In Me.TreeView1.Nodes
        If InStr(1, n.Key, "n" & nText) > 0 Then
            Set GetNode = n
            Exit Function
        End If
    Next
    Set GetNode = Nothing
End Function

I got the following result on my TreeView userform:
Screenshot 2022-02-05 204910.png


So, I think if this is what you want, the following code will work (with maybe a few tweaks if necessary:
VBA Code: 
Sub ProcName()
    Dim n As Node
   
    Me.TreeView1.Nodes.Clear
    Me.TreeView1.Nodes.Add , , "root1", Me.txtContractTitle.Value
   
    'load Treeview
    Do While Not rs.EOF
        Set n = GetNode("n" & rs.Fields.Item("BidItemNo"))
        If n Is Nothing Then
            Set n = Me.TreeView1.Nodes.Add("root1", tvwChild, "n" & rs.Fields.Item("BidItemNo"), rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription"))
        End If
        Me.TreeView1.Nodes.Add "n" & rs.Fields.Item("BidItemNo"), tvwChild, "n" & rs.Fields.Item("ActivityCode"), rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")
        rs.MoveNext
    Loop
   
    'Optional if you want to have all nodes expanded when the form displays
    For Each n In Me.TreeView1.Nodes
        n.Expanded = True
    Next n
End Sub

Function GetNode(nText As String) As Node
    Dim n As Node
    For Each n In Me.TreeView1.Nodes
        If n.Key = "n" & nText Then
            Set GetNode = n
            Exit Function
        End If
    Next
    Set GetNode = Nothing
End Function
 
Upvote 0
shknbk2 said:
I think I'm getting closer to understanding what you need. For testing, I made the following dataset:
BidItemNoBidItemDescriptionActivityCodeActivityDescription
10Concrete Curb1510Description for 1510
10Concrete Curb1511Description for 1511
10Concrete Curb1512Description for 1512
10Concrete Curb1513Description for 1513
10Concrete Curb1514Description for 1514
10Concrete Curb1515Description for 1515
20Concrete Curb and Gutter1516Description for 1516
20Concrete Curb and Gutter1517Description for 1517
20Concrete Curb and Gutter1518Description for 1518
20Concrete Curb and Gutter1519Description for 1519
20Concrete Curb and Gutter1520Description for 1520
30Concrete Sidewalk1521Description for 1521
30Concrete Sidewalk1522Description for 1522
30Concrete Sidewalk1523Description for 1523
30Concrete Sidewalk1524Description for 1524
30Concrete Sidewalk1525Description for 1525
30Concrete Sidewalk1526Description for 1526
40Sidewalk Trip Ledge Cutting1527Description for 1527
40Sidewalk Trip Ledge Cutting1528Description for 1528
40Sidewalk Trip Ledge Cutting1529Description for 1529
40Sidewalk Trip Ledge Cutting1530Description for 1530
40Sidewalk Trip Ledge Cutting1531Description for 1531


I made this code to test the idea:
VBA Code: 
Private Sub UserForm_Initialize()
    Dim c As Range
    Dim n As Node
  
    Me.TreeView1.Nodes.Clear
    Me.TreeView1.Nodes.Add , , "root1", "Contract Title"
    For Each c In Range("A2:A23")
        Set n = GetNode(c.Value)
        If n Is Nothing Then
            Set n = Me.TreeView1.Nodes.Add("root1", tvwChild, "n" & c.Value, c.Value & " - " & c.Offset(0, 1).Value)
        End If
        Me.TreeView1.Nodes.Add "n" & c.Value, tvwChild, "n" & c.Offset(0, 2), c.Offset(0, 2) & " : " & c.Offset(0, 3)
    Next c
    For Each n In Me.TreeView1.Nodes
        n.Expanded = True
    Next n
End Sub

Function GetNode(nText As String) As Node
    Dim n As Node
    For Each n In Me.TreeView1.Nodes
        If InStr(1, n.Key, "n" & nText) > 0 Then
            Set GetNode = n
            Exit Function
        End If
    Next
    Set GetNode = Nothing
End Function

I got the following result on my TreeView userform:
View attachment 57105

So, I think if this is what you want, the following code will work (with maybe a few tweaks if necessary:
VBA Code: 
Sub ProcName()
    Dim n As Node
  
    Me.TreeView1.Nodes.Clear
    Me.TreeView1.Nodes.Add , , "root1", Me.txtContractTitle.Value
  
    'load Treeview
    Do While Not rs.EOF
        Set n = GetNode("n" & rs.Fields.Item("BidItemNo"))
        If n Is Nothing Then
            Set n = Me.TreeView1.Nodes.Add("root1", tvwChild, "n" & rs.Fields.Item("BidItemNo"), rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription"))
        End If
        Me.TreeView1.Nodes.Add "n" & rs.Fields.Item("BidItemNo"), tvwChild, "n" & rs.Fields.Item("ActivityCode"), rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")
        rs.MoveNext
    Loop
  
    'Optional if you want to have all nodes expanded when the form displays
    For Each n In Me.TreeView1.Nodes
        n.Expanded = True
    Next n
End Sub

Function GetNode(nText As String) As Node
    Dim n As Node
    For Each n In Me.TreeView1.Nodes
        If n.Key = "n" & nText Then
            Set GetNode = n
            Exit Function
        End If
    Next
    Set GetNode = Nothing
End Function
Click to expand...

I really appreciate your assistance, I am pretty new to VBA.
This is exactly the outcome i am seeking.

Not sure why, but I am only getting the 1st record populating. I even made a brand new workbook and tried it.
I've attached the recordset from my ms sql database, the stored procedure I am using to populate with and a screen shot of the userform.
Maybe you can see something I am missing.

I thought maybe the fact that the "ActivityCode" can have duplicatation through the BidItems(1 Bid Item will only have an ActivityCode once) might be doing something, so I manipulated my data so there was no duplicate and it made no difference.

VBA Code: 
Sub ProcName()
Dim n As Node
Dim Conn1 As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim sql As String

    With Me.TreeView1
      .Appearance = ccFlat
      .CheckBoxes = False
      .LineStyle = tvwRootLines
      .Nodes.Clear
    End With

    Set Conn1 = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset

    Conn1.ConnectionString = SQLConStr
    Conn1.Open

    cmd.ActiveConnection = Conn1

    On Error GoTo ErrorHandler
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "spGetTeeview1Data"
    cmd.Parameters.Refresh
    cmd.NamedParameters = True
    cmd.Parameters("@EstimateID").Value = Me.txtEstimateNo.Value

    Set rs = cmd.Execute
   
    Me.TreeView1.Nodes.Clear
    Me.TreeView1.Nodes.Add , , "root1", Me.txtContractTitle.Value
   
    'load Treeview
    Do While Not rs.EOF
        Set n = GetNode("n" & rs.Fields.Item("BidItemNo"))
        If n Is Nothing Then
            Set n = Me.TreeView1.Nodes.Add("root1", tvwChild, "n" & rs.Fields.Item("BidItemNo"), rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription"))
        End If
        Me.TreeView1.Nodes.Add "n" & rs.Fields.Item("BidItemNo"), tvwChild, "n" & rs.Fields.Item("ActivityCode"), rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")
        rs.MoveNext
    Loop
   
    'Optional if you want to have all nodes expanded when the form displays
    For Each n In Me.TreeView1.Nodes
        n.Expanded = True
    Next n
    
    On Error GoTo 0

    Conn1.CommitTrans
    Conn1.Close

    Set Conn1 = Nothing
    Set rs = Nothing

    Exit Sub

ErrorHandler:

End Sub

Function GetNode(nText As String) As Node
    Dim n As Node
    For Each n In Me.TreeView1.Nodes
        If n.Key = "n" & nText Then
            Set GetNode = n
            Exit Function
        End If
    Next
    Set GetNode = Nothing
End Function

Private Sub CommandButton1_Click()

    Call ProcName

End Sub

[ATTACH type="full"]57109[/ATTACH][ATTACH type="full"]57107[/ATTACH][ATTACH type="full"]57108[/ATTACH]
 

Attachments

  • Recordset.png
    Recordset.png
    61 KB · Views: 77
  • Stored Procedure.png
    Stored Procedure.png
    24.2 KB · Views: 76
  • UserformTreeview.png
    UserformTreeview.png
    9.4 KB · Views: 77
Upvote 0
Since you're new to VBA, you might not be very familiar with debugging as the code is running. Look at a few online examples/tutorials (example) and use it to step through the code to see if you can follow what is happening. You can set a breakpoint on a line like the Do While line and run the code to the breakpoint, or you can just step through the code (F8) from the beginning of the code. It's not too long, so stepping through the whole thing shouldn't be too much trouble. Share what your findings are.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Has anyone used OpenLinks or ActualTech drivers?
Replies
0
Views
238
chuckbo
C
B
  • Question Question
Get Checked Listview Items from Form to populate Treeview in a Different Form
2
Replies
12
Views
2K
Fluff
Fluff
K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
674
kbishop94
K
D
  • Solved
Userform: Populating Cells based on the information selected in the Combobox
Replies
9
Views
558
iantech
I
J
  • Question Question
Is it possible to link a TreeView with a ComboBox? VBA
Replies
0
Views
713
jmlgmbn
J

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top