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

 

[bhalbach]

Yes, it still exists. If you want to populate other information if you didn't grab it the first time through while populating the treeview, you'll have to go back in and get it every time unless you can find a way to store it somewhere.

So I could run the stored procedure on click of a node and have other fields populated at the same time as this last code you wrote?

 

[shknbk2]

What is an example of the other fields you want populated?

 

[bhalbach]

Hmm…I referenced a recordset, but this was for


Works like it should awesome.

I had mentioned Recordset in the last message and then I thought about it a bit....the recordset was related to populating the treeview. At this point does the recordset still exist or is it gone when the Sub Procedure to populate the treeview was completed? If it still exists, there are other fields that could be populate when the node is clicked.

There are also other fields that I can have in the same recordset that can populate at the same time as the node click are

Yes, it still exists. If you want to populate other information if you didn't grab it the first time through while populating the treeview, you'll have to go back in and get it every time unless you can find a way to store it somewhere.

I'm thinking I could potentially run a stored procedure in the TreeView1_NodeClick and populate the fields that I have in Blue Text at the same time as the rest of the NodeClick code you wrote.

The incomplete information I gave you...
-If the Node that is Selected is a BidItem then Textbox35 is to populate with BidItemCode and Label163 is to populate with BidItemDescription. ActivityCode is to be blank and ActivityItemDescription is to be Blank. There are also other fields that I can have in the same recordset that can populate at the same time...Label164 is to populate with TakeOffQuantity, Label165 is to populate with BidItemUOM, Label168 populate with BidItemQuantity

-If the Node that is selected is an ActivityItem then Textbox35 is to populate with the BidItemCode of its Parent and Label163 is to populate with BidItemDescription of its Parent...AND TextBox49 is to populate with the selected Node ActivityItemCode and Textbox48 is to populate with the selected Node ActivityItemDescription. There are also other fields that I can have in the same recordset that can populate at the same time...TextBox47 is to populate with ActivityItemQuantity, textBox46 is to populate with ActivityItemUOM

 

[shknbk2]

How will the code determine which recordset to get the extra data from? If there are many records with ActivityCode 10, which one has the TakeOffQuantity you want to use?

 

[bhalbach]

How will the code determine which recordset to get the extra data from? If there are many records with ActivityCode 10, which one has the TakeOffQuantity you want to use?

The recordset I am use has a parameter that is input that only creates a recordset relative to the current “Estimate” I am working on.

 

[shknbk2]

When you click on a node, the code can go back into the data to get the missing fields. As the code is looping through the records (like when populating the treeview), how will the code know when the record that belongs to the data you want is found?

 

[bhalbach]

When you click on a node, the code can go back into the data to get the missing fields. As the code is looping through the records (like when populating the treeview), how will the code know when the record that belongs to the data you want is

I was hoping that the BidItemNo and ActivityItemCode could be used on the selected Node. But, the ActivityItemCode will be repeated unfortunately.

How about a change event on the populated textboxes from the Treeview1_NodeClick. Textbox35 has the BidItemNo and TextBox49 has the ActivityItemCode.

If it is a BidItem node TextBox35 will have a value and the BidItemQuantity/BitItemUOM could be found in the recordset that way. Same with if it is a ActivityItem node, both the TextBox35(BidItemNo) and TextBox49(ActivityItemCode) would be there, then find in the recordset these values and bring back ActivityQuantity to TextBox47 and ActivityUOM to Textbox46.

I’m not really sure how this can be achieved, just thinking.

 

[shknbk2]

Please provide another sample dataset with the extra values included. In your earlier workbook, Bid and Activity were included. To help out the analysis, the extra data would be helpful to see where it is located in the data in relation to Bid and Activity values.

 

[bhalbach]

Please provide another sample dataset with the extra values included. In your earlier workbook, Bid and Activity were included. To help out the analysis, the extra data would be helpful to see where it is located in the data in relation to Bid and Activity values.

See attached link for the dataset.

I also setup the form so it currently works as it should with the newest code.

Thanks for your assistance, really appreciated.

 

[shknbk2]

No attached link.