Tree view adding duplcate keys

[ghanley]

Hi Guys,

I need some help with a problem related to the looping through a
recordset to display on a tree view control.

My table has no Primary key - because duplicates are allowed.

ID parent
1
2 1
3 1
4 1
5
1 5

I would like to display this in a treeview like such

1
- 2
- 3
- 4
5
- 1
- 2
- 3
- 4

The code I am using below throws error 35602 as soon as it tries to
start list the 1 series of numbers.

Does anyone know of a way to code around this please??

Private Sub Form_Load()
Const strTableQueryName = "query2"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset,
dbReadOnly)
AddBranch rst:=rst, strPointerField:="parent", strIDField:="DBaseID",
strTextField:="text2" ', varReportToID:="DBaseID"
End Sub

'================= AddBranch Sub Procedure ======================
' Recursive Procedure to add branches to TreeView Control
'Requires:
' ActiveX Control: TreeView Control
' Name: xTree
'Parameters:
' rst: Self-referencing Recordset containing the data
' strPointerField: Name of field pointing to parent's primary key
' strIDField: Name of parent's primary key field
' strTextField: Name of field containing text to be displayed
'================================================= ============
Sub AddBranch(rst As Recordset, strPointerField As String, strIDField
As String, strTextField As String, _
Optional varReportToID As Variant)
On Error GoTo ErrorHandler

Dim x
' On Error Resume Next

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strKey As String
Dim nodParent As Node, bk As String
Set objTree = Me!xTree.Object
If IsMissing(varReportToID) Then ' Root Branch.
strCriteria = strPointerField & " Is Null"
Else ' Search for records pointing to parent.
strCriteria = BuildCriteria(strPointerField,
rst.Fields(strPointerField).Type, "=" & varReportToID)
Set nodParent = objTree.Nodes("a" & varReportToID)
End If

' Find the first emp to report to the boss node.
rst.FindFirst strCriteria
Do Until rst.NoMatch
' Create a string with LastName.
If Not IsNull(rst(strTextField)) Then
strText = rst(strTextField)
Else
strText = "No Text"
End If

strKey = "a" & rst(strIDField)
If Not IsMissing(varReportToID) Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, 4, strKey,
strText)
Else ' Add new node to the root.
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
End If
' Save your place in the recordset so we can pass by ref for
speed.
bk = rst.Bookmark
' Add employees who report to this node.
AddBranch rst, strPointerField, strIDField, strTextField,
rst(strIDField)
rst.Bookmark = bk ' Return to last place and continue search.
rst.FindNext strCriteria ' Find next employee.
Loop

ErrorHandler:
Select Case Err.Number
Case 35602
MsgBox rst(strIDField)
Resume Next
Case 0
Case Else
MsgBox Err.Number & " " & Err.Description

End Select

End Sub
Regards

Gary

[Kevin Rollo]

Can you add an "AutoNumber" field to your table so you have a unique key?
This then can be uses as the hidden 'key' for the treeview

strKey = "a" & rst(AutoNumberField)
strText = rst(TextField)
Set nodCurrent = objTree.Nodes.Add(nodParent, 4, strKey, strText)

You will need to reference the "parent" to the autonumber value as well

--
Regards,
Kevin
"ghanley" <gh*****@hcsfixit.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi Guys,

I need some help with a problem related to the looping through a
recordset to display on a tree view control.

My table has no Primary key - because duplicates are allowed.

ID parent
1
2 1
3 1
4 1
5
1 5

I would like to display this in a treeview like such

1
- 2
- 3
- 4
5
- 1
- 2
- 3
- 4

The code I am using below throws error 35602 as soon as it tries to
start list the 1 series of numbers.

Does anyone know of a way to code around this please??

Private Sub Form_Load()
Const strTableQueryName = "query2"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset,
dbReadOnly)
AddBranch rst:=rst, strPointerField:="parent", strIDField:="DBaseID",
strTextField:="text2" ', varReportToID:="DBaseID"
End Sub

'================= AddBranch Sub Procedure ======================
' Recursive Procedure to add branches to TreeView Control
'Requires:
' ActiveX Control: TreeView Control
' Name: xTree
'Parameters:
' rst: Self-referencing Recordset containing the data
' strPointerField: Name of field pointing to parent's primary key
' strIDField: Name of parent's primary key field
' strTextField: Name of field containing text to be displayed
'================================================= ============
Sub AddBranch(rst As Recordset, strPointerField As String, strIDField
As String, strTextField As String, _
Optional varReportToID As Variant)
On Error GoTo ErrorHandler

Dim x
' On Error Resume Next

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strKey As String
Dim nodParent As Node, bk As String
Set objTree = Me!xTree.Object
If IsMissing(varReportToID) Then ' Root Branch.
strCriteria = strPointerField & " Is Null"
Else ' Search for records pointing to parent.
strCriteria = BuildCriteria(strPointerField,
rst.Fields(strPointerField).Type, "=" & varReportToID)
Set nodParent = objTree.Nodes("a" & varReportToID)
End If

' Find the first emp to report to the boss node.
rst.FindFirst strCriteria
Do Until rst.NoMatch
' Create a string with LastName.
If Not IsNull(rst(strTextField)) Then
strText = rst(strTextField)
Else
strText = "No Text"
End If

strKey = "a" & rst(strIDField)
If Not IsMissing(varReportToID) Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, 4, strKey,
strText)
Else ' Add new node to the root.
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
End If
' Save your place in the recordset so we can pass by ref for
speed.
bk = rst.Bookmark
' Add employees who report to this node.
AddBranch rst, strPointerField, strIDField, strTextField,
rst(strIDField)
rst.Bookmark = bk ' Return to last place and continue search.
rst.FindNext strCriteria ' Find next employee.
Loop

ErrorHandler:
Select Case Err.Number
Case 35602
MsgBox rst(strIDField)
Resume Next
Case 0
Case Else
MsgBox Err.Number & " " & Err.Description

End Select

End Sub
Regards

Gary

[Gary Hanley]

Kevin - thanks for your reply.

This I believe will only solve half of my problem.

In essence what I am trying to acheive is that I build an assembly - or
a tree like

1
- 2
- 3
-- 4

This tree may be a sub branch of 5 like

5
- 1
-- 2
-- 3
--- 4

What I want to acheive is by assigning the assembly or the tree 1 to
another parent assembly - it will list all the sub branches with the one
allocation.

So

6
-5

only allocated once

would actually list as

6
- 5
-- 1
--- 2
--- 3
---- 4

and in the table it would be entered like

AutoNum ID parent
1 1
2 2 1
3 3 1
4 4 3
5 5
6 1 5
7 6
8 5 6
Regards

Gary

*** Sent via Developersdex http://www.developersdex.com ***

[Kevin Rollo]

I assume then you are dealing with a "sub-assembly" tree (or components)
that can be associated to more than one 'Parent assemblies'

To cut to the chase, Treeview wants a unique key, and I guess the autonumber
isn't going to do it for you easily. You should however be able to get a
unique string by joining fields together

where you currently have .... strKey = "a" & rst(strIDField)
try something like ... strKey = rst(autonumberfield) & "-" &
rstr(strIDField)

alternatively, you could change the prefix character every time you start a
new Parent Node.
Using a counter, start the first node with "1-", the second is "2-" etc

strKey = intCount & "-" & rstr(strIDField)
If Not IsMissing(varReportToID) Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, 4, strKey, strText)
intCount = intCount + 1
else
...
--
Regards,
Kevin

"Gary Hanley" <gh*****@hcsfixit.com> wrote in message
news:SL***************@news.uswest.net...
Kevin - thanks for your reply.

This I believe will only solve half of my problem.

In essence what I am trying to acheive is that I build an assembly - or
a tree like

1
- 2
- 3
-- 4

This tree may be a sub branch of 5 like

5
- 1
-- 2
-- 3
--- 4

What I want to acheive is by assigning the assembly or the tree 1 to
another parent assembly - it will list all the sub branches with the one
allocation.

So

6
-5

only allocated once

would actually list as

6
- 5
-- 1
--- 2
--- 3
---- 4

and in the table it would be entered like

AutoNum ID parent
1 1
2 2 1
3 3 1
4 4 3
5 5
6 1 5
7 6
8 5 6
Regards

Gary

*** Sent via Developersdex http://www.developersdex.com ***

[Mondrogan]

I think what you have is a diagram (a "regular set") but not a tree
structure. A node can't have children which are also ancestors, that is to
say, it can't point "backward".

"ghanley" <gh*****@hcsfixit.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi Guys,

I need some help with a problem related to the looping through a
recordset to display on a tree view control.

My table has no Primary key - because duplicates are allowed.

ID parent
1
2 1
3 1
4 1
5
1 5

I would like to display this in a treeview like such

1
- 2
- 3
- 4
5
- 1
- 2
- 3
- 4

The code I am using below throws error 35602 as soon as it tries to
start list the 1 series of numbers.

Does anyone know of a way to code around this please??

Private Sub Form_Load()
Const strTableQueryName = "query2"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset,
dbReadOnly)
AddBranch rst:=rst, strPointerField:="parent", strIDField:="DBaseID",
strTextField:="text2" ', varReportToID:="DBaseID"
End Sub

'================= AddBranch Sub Procedure ======================
' Recursive Procedure to add branches to TreeView Control
'Requires:
' ActiveX Control: TreeView Control
' Name: xTree
'Parameters:
' rst: Self-referencing Recordset containing the data
' strPointerField: Name of field pointing to parent's primary key
' strIDField: Name of parent's primary key field
' strTextField: Name of field containing text to be displayed
'================================================= ============
Sub AddBranch(rst As Recordset, strPointerField As String, strIDField
As String, strTextField As String, _
Optional varReportToID As Variant)
On Error GoTo ErrorHandler

Dim x
' On Error Resume Next

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strKey As String
Dim nodParent As Node, bk As String
Set objTree = Me!xTree.Object
If IsMissing(varReportToID) Then ' Root Branch.
strCriteria = strPointerField & " Is Null"
Else ' Search for records pointing to parent.
strCriteria = BuildCriteria(strPointerField,
rst.Fields(strPointerField).Type, "=" & varReportToID)
Set nodParent = objTree.Nodes("a" & varReportToID)
End If

' Find the first emp to report to the boss node.
rst.FindFirst strCriteria
Do Until rst.NoMatch
' Create a string with LastName.
If Not IsNull(rst(strTextField)) Then
strText = rst(strTextField)
Else
strText = "No Text"
End If

strKey = "a" & rst(strIDField)
If Not IsMissing(varReportToID) Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, 4, strKey,
strText)
Else ' Add new node to the root.
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
End If
' Save your place in the recordset so we can pass by ref for
speed.
bk = rst.Bookmark
' Add employees who report to this node.
AddBranch rst, strPointerField, strIDField, strTextField,
rst(strIDField)
rst.Bookmark = bk ' Return to last place and continue search.
rst.FindNext strCriteria ' Find next employee.
Loop

ErrorHandler:
Select Case Err.Number
Case 35602
MsgBox rst(strIDField)
Resume Next
Case 0
Case Else
MsgBox Err.Number & " " & Err.Description

End Select

End Sub
Regards

Gary