?
Solved

TreeView BOM

Posted on 2009-04-29
2
Medium Priority
?
982 Views
Last Modified: 2013-11-28
I am attempting to populate a Treeview to display a Build of Materials.

I used the code from Microsoft which works well to display Employee-Supervisor Relationships. However when a Part# is used in different subassemblies I get the error #: 35602 - Key is not unique. (==> in the code below is where the error occurs)

Could an expert please assist me in pointing out the changes I need to make in the code.

I have a form with 2 controls: <cmLookForID> that displays a <Product ID>  that a user can select to display its expanded BOM. The 2nd control is the <xTree> TreeView control.

Private Sub cmbLookForID_AfterUpdate()

  Dim cTableQueryName As String
  cTableQueryName = "qProduct"
 
  Dim db As DAO.Database, rs As DAO.Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordset(cTableQueryName, dbOpenDynaset, dbReadOnly)
 
  Dim ctl As Control, oTree As TreeView
  Set ctl = Me!xTree
  Set oTree = ctl.Object
  oTree.Nodes.Clear
 
  SbAddBranch rs:=rs, _
                         cPointerField:="ParentID", _
                         cIDField:="ChildID", _
                         cTextField:="ChildName"

End Sub

'================= AddBranch Sub Procedure ======================
'  Recursive Procedure to add branches to TreeView Control
'  rs:                      Self-referencing Recordset containing the data
'  cPointerField:     Name of field pointing to parent's primary key
'  cIDField:            Name of parent's primary key field
'  cTextField:        Name of field containing text to be displayed
'=============================================================
'
Sub SbAddBranch(rs As Recordset, cPointerField As String, cIDField As String, cTextField As String, Optional vReportToID As Variant)
On Error GoTo ErrAddBranch
   
   Dim ndCurrent As Node, oTree As TreeView
   Dim strCriteria As String, cText As String, cKey As String
   Dim ndParent As Node, bk As String
   
   ' -- Identify the TreeView Object
   Set oTree = Me!xTree.Object
   
   If IsMissing(vReportToID) Then
      strCriteria = cPointerField & "=" & Me![cmbLookForID]
   Else
      strCriteria = BuildCriteria(cPointerField, rs.Fields(cPointerField).Type, "=" & vReportToID)
      Set ndParent = oTree.Nodes("k" & vReportToID)
   End If

   rs.FindFirst strCriteria
   Do Until rs.NoMatch
       ' -- Create a string with LastName.
      cText = rs(cTextField)
      cKey = "k" & rs(cIDField)
     
      ' -- Add new node to the parent
      If Not IsMissing(vReportToID) Then
==>  Set ndCurrent = oTree.Nodes.Add(ndParent, tvwChild, cKey, cText)
       
      ' -- Add new node to the root.
      Else
==>  Set ndCurrent = oTree.Nodes.Add(, , cKey, cText)
      End If
     
      bk = rs.Bookmark
      SbAddBranch rs, cPointerField, cIDField, cTextField, rs(cIDField)
      rs.Bookmark = bk
      rs.FindNext strCriteria
  Loop
   
ExitAddBranch:
   Exit Sub

ErrAddBranch:
   
   MsgBox "Can't add child:  " & Err.Number & ": " & Err.Description, vbCritical, "AddBranch Error:"
   Resume ExitAddBranch

End Sub

Structure of the <qProduct> which is the datasource is displayed below:
ParentID  ChildID    ChildName
200-001  200-002
200-001  200-005
200-001  200-030
200-001  200-031
200-001  950-030
200-001  950-055

200-002  200-012
200-002  200-018
200-002  200-026
200-002  200-029

200-018  200-019
200-018  200-020
200-018  200-022
200-018  999-047

200-026  200-011
200-026  200-012
200-026  200-013
200-026  200-025
         
200-025  200-011
200-025  200-012
200-025  200-013
200-025  200-024

200-024  200-007
200-024  200-008
200-024  200-011
200-024  200-012
200-024  200-013
200-024  200-018
200-024  200-023

200-030  001-058
200-030  200-003

001-058  001-048

001-048  001-057

200-031  200-004


Sample Treeview that should be displayed, BUT I cannot is displayed below. The counter, and code in parenthesis and * for end-nod are just for illustration - I just need the parts to display in a tree-view hierarchial fashion.

200-001 (#142)
  (1) 200-002 (#143)
          (1) 200-012 (#036) *
          (2) 200-018 (#230)
                    (1) 200-019 (#128) *
                    (2) 200-020 (#134) *
                    (3) 200-022 (#004) *
                    (4) 999-047 (#044) *
          (3) 200-026 (#234)
                    (1) 200-011 (#127) *
                    (2) 200-012 (#036) *
                    (3) 200-013 (#099) *
                    (4) 200-025 (#233)
                            (1) 200-011 (#127) *
                            (2) 200-012 (#036) *
                            (3) 200-013 (#099) *
                            (4) 200-024 (#232)
                                      (1) 200-007 (#094) *
                                      (2) 200-008 (#100) *
                                      (3) 200-011 (#127) *
                                      (4) 200-012 (#036) *
                                      (5) 200-013 (#099) *
                                      (6) 200-018 (#230)              
                                             (1) 200-019 (#128) *
                                             (2) 200-020 (#134) *
                                             (3) 200-022 (#004) *
                                             (4) 999-047 (#044) *
                                      (7) 200-023 (#098) *
          (4) 200-029 (#364) *
  (2) 200-005 (#157) *
  (3) 200-030 (#372)
          (1) 001-058 (#236)
                    (1) 001-048 (#148)
                           (1) 001-057 (#235) *
          (2) 200-003 (#147) *
  (4) 200-031 (#466)
           (1) 200-004 (#149) *
  (5) 950-030 (#218) *
  (6) 950-055 (#288) *
0
Comment
Question by:chandgy2
2 Comments
 

Accepted Solution

by:
chandgy2 earned 0 total points
ID: 24262821
I followed the thread you recommended but still do not know how to make the necessary changes - I know what is causing the issue, the <key> must be unique in the entrie tree. Any help would be greatly appreciated. Thanks, GC
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question