Unique Key in Treeview

[Surveyor in VA via AccessMonster.com]

Hello-

I have been scanning the previous posts and I am not sure where my error is
that is causing the Error 35602: Key is not unique in collection.

I have the following code in which an error is called at the line:
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!SACCatID, strText)
under my Form_Load event. Reading up on the treeview control I thought by
using the "a" prefix to the autonumber of the record would stop this error.

and for the child node adding the a unique prefix to the autonumber of my
second table as shown here:
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & rst!SACCatID
& "b" & rst!SACSubID, strText)

However it does not. If anyone could help this would be a great help,
Thanks,
Chris F.
------------------------------------------------------------
Private Sub Form_Load()

Dim db As Database
Dim rst As Recordset
Dim nodCurrent As MSComctlLib.Node, nodRoot As MSComctlLib.Node
Dim objTree
Dim strText As String, bk As String
Dim intCheck As Integer

intCheck = 2 ' Replace with TempVars!CSAChecklistID

Set db = CurrentDb

Set rst = db.OpenRecordset("t7StandardChecklistCategory", dbOpenDynaset,
dbReadOnly)

Set objTree = Me!xTree.Object

With objTree
.Font.Name = "Calibri"
.Font.Size = 10
End With

rst.FindFirst "[SAChecklistID] =" & intCheck

Do Until rst.NoMatch
'Extract the supervisor's name.
strText = rst![OrderNo] & (".) " & rst![CategoryName])
'Add a root level node to the tree for the supervisor.
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!SACCatID,
strText)


'Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
'Run a recursive procedure to add all the child nodes
AddChildren nodCurrent

'Return to your placeholder.
rst.Bookmark = bk

rst.FindNext "[SAChecklistID] =" & intCheck
Loop

End Sub
-----------------------------------------------------------------------------
Sub AddChildren(nodBoss As MSComctlLib.Node)

Dim db As Database
Dim rst As Recordset
Dim nodCurrent As MSComctlLib.Node
Dim objTree
Dim strText As String, bk As String

Set db = CurrentDb

Set rst = db.OpenRecordset("t7StandardChecklistSubCat", dbOpenDynaset,
dbReadOnly)

'Create a reference to the TreeView control.
Set objTree = Me!xTree.Object

rst.FindFirst "[SACCatID] =" & Mid(nodBoss.Key, 2)

Do Until rst.NoMatch
'Extract the employee's name.
strText = rst![SubOrderNo] & (".) " + rst![SubCatName])
'Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & rst!
SACCatID & "b" & rst!SACSubID, strText)

'Save your place in the recordset.
bk = rst.Bookmark
'Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent
'Return to your place in the recordset and continue to search.
rst.Bookmark = bk
'Find the next employee who reports to this supervisor.
rst.FindNext "[SACCatID]=" & Mid(nodBoss.Key, 2)
Loop

End Sub

 

[Dale Fye]

What does your data look like, specifically
SACCatID SACSubID

It looks like you have duplicates of these two columns. Have you run a
FindDuplicates query, where you included both of these fields? That is where
I would start.

Personally, I include a WHERE clause in my SQL statements so that every
record that gets returned is one that needs to be added to the tree. That
way, I can just use rs.movenext rather than rs.FindNext. So, you could avoid
some work by using

strSQL = "SELECT * FROM t7StandardChecklistSubCat " _
& "WHERE [SACCatID] = " & mid(nodBoss.Key, 2)
set rst = db.openrecordset(strsql, ...

Also, you should not need the statements:

bk = rst.Bookmark
and
rst.Bookmark = bk


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello-

I have been scanning the previous posts and I am not sure where my error is
that is causing the Error 35602: Key is not unique in collection.

I have the following code in which an error is called at the line:
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!SACCatID, strText)
under my Form_Load event. Reading up on the treeview control I thought by
using the "a" prefix to the autonumber of the record would stop this error.

and for the child node adding the a unique prefix to the autonumber of my
second table as shown here:
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & rst!SACCatID
& "b" & rst!SACSubID, strText)

However it does not. If anyone could help this would be a great help,
Thanks,
Chris F.
------------------------------------------------------------
Private Sub Form_Load()

Dim db As Database
Dim rst As Recordset
Dim nodCurrent As MSComctlLib.Node, nodRoot As MSComctlLib.Node
Dim objTree
Dim strText As String, bk As String
Dim intCheck As Integer

intCheck = 2 ' Replace with TempVars!CSAChecklistID

Set db = CurrentDb

Set rst = db.OpenRecordset("t7StandardChecklistCategory", dbOpenDynaset,
dbReadOnly)

Set objTree = Me!xTree.Object

With objTree
.Font.Name = "Calibri"
.Font.Size = 10
End With

rst.FindFirst "[SAChecklistID] =" & intCheck

Do Until rst.NoMatch
'Extract the supervisor's name.
strText = rst![OrderNo] & (".) " & rst![CategoryName])
'Add a root level node to the tree for the supervisor.
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst!SACCatID,
strText)


'Use a placeholder to save this place in the recordset.
bk = rst.Bookmark
'Run a recursive procedure to add all the child nodes
AddChildren nodCurrent

'Return to your placeholder.
rst.Bookmark = bk

rst.FindNext "[SAChecklistID] =" & intCheck
Loop

End Sub
-----------------------------------------------------------------------------
Sub AddChildren(nodBoss As MSComctlLib.Node)

Dim db As Database
Dim rst As Recordset
Dim nodCurrent As MSComctlLib.Node
Dim objTree
Dim strText As String, bk As String

Set db = CurrentDb

Set rst = db.OpenRecordset("t7StandardChecklistSubCat", dbOpenDynaset,
dbReadOnly)

'Create a reference to the TreeView control.
Set objTree = Me!xTree.Object

rst.FindFirst "[SACCatID] =" & Mid(nodBoss.Key, 2)

Do Until rst.NoMatch
'Extract the employee's name.
strText = rst![SubOrderNo] & (".) " + rst![SubCatName])
'Add as a child node to the tree.
Set nodCurrent = objTree.Nodes.Add(nodBoss, tvwChild, "a" & rst!
SACCatID & "b" & rst!SACSubID, strText)

'Save your place in the recordset.
bk = rst.Bookmark
'Add any employees for whom the current node is a supervisor.
AddChildren nodCurrent
'Return to your place in the recordset and continue to search.
rst.Bookmark = bk
'Find the next employee who reports to this supervisor.
rst.FindNext "[SACCatID]=" & Mid(nodBoss.Key, 2)
Loop

End Sub

 

[Surveyor in Va via AccessMonster.com]

Hi Dale-

I appreciate the help. After reviewing below I started removing the bk=rst.
Bookmark and noticed on the next line I was making a call to the function I
was just in, wanting to populate the same child nodes over, hence the error
message. After removing that line of code, it appears to work fine.

Again thank you for your help. I will be modifying my code with the SQL
statements as you suggested.
Chris F

What does your data look like, specifically
SACCatID SACSubID

It looks like you have duplicates of these two columns. Have you run a
FindDuplicates query, where you included both of these fields? That is where
I would start.

Personally, I include a WHERE clause in my SQL statements so that every
record that gets returned is one that needs to be added to the tree. That
way, I can just use rs.movenext rather than rs.FindNext. So, you could avoid
some work by using

strSQL = "SELECT * FROM t7StandardChecklistSubCat " _
& "WHERE [SACCatID] = " & mid(nodBoss.Key, 2)
set rst = db.openrecordset(strsql, ...

Also, you should not need the statements:

bk = rst.Bookmark
and
rst.Bookmark = bk

Hello-

[quoted text clipped - 97 lines]

End Sub