We help IT Professionals succeed at work.
Get Started


dupree4 asked
Last Modified: 2010-08-05
I'm almost there with my treeview.  The problem is that this code is designed for employees reporting to other employees and such, so it does not completely work for my bill of material situation, where a component can be a component of more than one assembly.  In other words, like an employeee reporting to more than one employee.

How can I fix it so it works this way


see...in the code below i get an error says "Cant Add child:  Key is not unique".  I want to make it so it doesnt have to be unique...so that CCCC can be a component of more than 1 assembly.  Any ideas?  The *** marks the line of code the error occurs:

Option Compare Database

Option Explicit

'=================Load Event for the Form=======================
'Initiates the routine to fill the TreeView control

Private Sub Form_Load()

Const strTableQueryName = "Relationships"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)
AddBranch rst:=rst, strPointerField:="ParentID", strIDField:="ChildID", strTextField:="ChildID"
End Sub

'================= AddBranch Sub Procedure ======================
'      Recursive Procedure to add branches to TreeView Control
'   ActiveX Control:  TreeView Control
'              Name:  xTree
'               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 DAO.Recordset, strPointerField As String, _
              strIDField As String, strTextField As String, _
              Optional varReportToID As Variant)
   On Error GoTo errAddBranch
   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.
      strText = rst(strTextField)
      strKey = "a" & rst(strIDField)
      If Not IsMissing(varReportToID) Then  'add new node to the parent
***         Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, 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.

   Exit Sub

      '--------------------------Error Trapping --------------------------
   MsgBox "Can't add child:  " & Err.Description, vbCritical, "AddBranch Error:"
   Resume exitAddBranch
End Sub
Watch Question
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE