VBA to Populate Excel Userform Treeview from MS SQL

[bhalbach]

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.

    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

 

[shknbk2]

I got the BrentApp.bak file, but I can't do anything with it. I don't have an SQL server running. Is there any way you can convert it to Access?

 

[bhalbach]

I got the BrentApp.bak file, but I can't do anything with it. I don't have an SQL server running. Is there any way you can convert it to Access?

I don’t have access or know how to do that. Would a copy of the data in excel sheet help? I could get the stored procedure data into worksheets.

 

[shknbk2]

That would be even better.

 

[bhalbach]

That would be even better.

Should only need the data that the spGetTreeviewData provides?

 

[shknbk2]

Whatever data will be needed to experience the error.

 

[bhalbach]

Whatever data will be needed to experience the error.

I put the data that drives the Treeview and listview in the treeview test stand-alone workbook. It’s in the linked folder on dropbox

 

[shknbk2]

I converted the data into an Access database and had to manipulate the connection code to accommodate that. However, in the pictures below, you can see that the first subnode's tag is set when the tree is populated and is later recalled in the tagArray variable. My code couldn't further process the "cmd.Parameters("@EstimateID").Value" and similar code, but the tagArray's values are correct. Therefore, I can't recreate your issue where tagArray(0) loses its value between that line and the assignment line at the beginning of the procedure.

This is my Access table from where the nodes are getting their values/tags.

This shows that the first subnode tag is being set to these values

I added an extra MsgBox code to show the tag values when the nodes are selected

Here is the userform after clicking on the 1510 node


After clicking Add Activity Form, I randomly selected 27


Clicking OK sets the tagArray, the cursor shows the Tag value


All the values in tagArray


The value of tagArray(0) down in the code part I can't run (the parameters.value portion doesn't work well with the Access DB, I think. I wasn't going to play around to find out more. I just wanted to verify that the selectedNode tag comes through)

 

[bhalbach]

I converted the data into an Access database and had to manipulate the connection code to accommodate that. However, in the pictures below, you can see that the first subnode's tag is set when the tree is populated and is later recalled in the tagArray variable. My code couldn't further process the "cmd.Parameters("@EstimateID").Value" and similar code, but the tagArray's values are correct. Therefore, I can't recreate your issue where tagArray(0) loses its value between that line and the assignment line at the beginning of the procedure.

This is my Access table from where the nodes are getting their values/tags. View attachment 57919

This shows that the first subnode tag is being set to these values
View attachment 57920

I added an extra MsgBox code to show the tag values when the nodes are selected
View attachment 57921

Here is the userform after clicking on the 1510 node
View attachment 57927

After clicking Add Activity Form, I randomly selected 27
View attachment 57923

Clicking OK sets the tagArray, the cursor shows the Tag value
View attachment 57924

All the values in tagArray
View attachment 57925

The value of tagArray(0) down in the code part I can't run (the parameters.value portion doesn't work well with the Access DB, I think. I wasn't going to play around to find out more. I just wanted to verify that the selectedNode tag comes through)
View attachment 57926

That’s with the same code? Strange why it’s doing this.

 

[bhalbach]

That’s with the same code? Strange why it’s doing this.

If I do as you did and click on 1510, the tagArray works.

If I click on BidItem 10 - Concrete Curb and try and add an Activity to BidItem 10 that is when the tag Array does not work.

 

[shknbk2]

The code doesn't set the tag for those bidItem nodes, so there isn't any text to retrieve. You'll have to copy the "Set n" style from the activity nodes to the bidItem nodes.

    Do While Not rs.EOF
        keyBidItem = "BIN" & rs.Fields.Item("BidItemNo")
        nodeText = rs.Fields.Item("BidItemNo") & " - " & rs.Fields.Item("BidItemDescription")
        If Not NodeExists(keyBidItem) Then
            Me.TreeView1.Nodes.Add "root1", tvwChild, keyBidItem, nodeText
        End If
       
        keyActivity = keyBidItem & "AC" & rs.Fields.Item("ActivityCode")
        nodeText = rs.Fields.Item("ActivityCode") & " : " & rs.Fields.Item("ActivityDescription")
        'Check for null
        If nodeText <> " : " Then
            'Took this code out....
            'Me.TreeView1.Nodes.Add keyBidItem, tvwChild, keyActivity, nodeText
            'Replaced with this....
            Set n = Me.TreeView1.Nodes.Add(keyBidItem, tvwChild, keyActivity, nodeText) 'Replaced with this code
            n.Tag = rs.Fields.Item("BidItemID") & "," & rs.Fields.Item("ActivityID") & "," & rs.Fields.Item("BidItemNo") & "," & rs.Fields.Item("BidItemQuantity") & "," & rs.Fields.Item("BidItemUOM") & "," & rs.Fields.Item("TakeOffQuantity")
        End If