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]
I believe the issue is the keys are not unique.
When I comment out the "On Error Go To Error Handler" I get Run-time error '35602' Key is Not Unique in the collection.
I am thinking because the BidItemNo and the ActivityItemCode are not unique through the dataset. Leaning towards the ActivityItemCode causing the problem but I am not familiar enough with vba to follow your code...but im playing with it. Any help would be really great.
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.
I know what the issue is, i did some trials and have determined that if the "ActivityCode" is not Unique the treeview will error out with Run-time error '35602' Key is not unique in collection.
The ActivityCode will repeat throughout the recordset in most cases, as well A Null may be returned in the recordset therefore the Null is not unique either.
-When an ActivityCode is not Unique in the recordset, the error will occur. ActivityCodes will repeat in the recordset multiple times.
-Null in a recordset will occur likely multiple times, if "Null" in the ActivityCode occurs more than once then the error occurs because then Null is not unique.
-Repeated ActivityDescription has no effect and it works as long as the ActivityCode is unique.
-If the ActivityCode is Null, only the BidItemNo and BidItemSecription should populate the treeview and the related ActivityCode and ActivityDescription should not be populated.
Also, I will be having more childs for the tree...for example the ActivityCode/ActivityDescription will end up being a Parent to ResourceCode/ResourceActivity. Possibly one more level as well.
BidItemNo/BidItemDescription
.........ActivityCode/ActivityDescription
.................ResourceCode/ResourceDescription
.........................NewLevelCode/NewLevelDescription
I am struggling to understand your code, so I haven't been able to make the necessary corrections...I've spent hours on this already ugh.
I tried to attach a snip of the table I was using in excel for the trial but it MrExcel says it is too large.
