TreeView BOM (Error #35602)

[ccg]

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 I use this code to build a BOM it does not work. This is
because in a BOM 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 get it to; is
displayed below. The counter, and code in parenthesis and * for end-
node are just for illustration - I just need the parts to display in a
tree-view hierarchial fashion. My basic problem is that I end up with
duplicate key value under different roots which the treeview does not
allow.


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) *

 

[Tom van Stiphout]

(without reading all your code)
It appears you're currently using PartNo as the Key for the treeview
node. Since keys need to be unique that's not a good option. Think of
a better one.
Perhaps you can concatenate a few values that together are unique. I
once did this with a query-string-like format and it worked very well.
My key was something like:
CompanyID=1&LocationID=2&ContactID=3
Also great when user clicks on a node: you can simply parse the key
and know all important info on what was clicked on.

-Tom.
Microsoft Access MVP

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 I use this code to build a BOM it does not work. This is
because in a BOM 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 get it to; is
displayed below. The counter, and code in parenthesis and * for end-
node are just for illustration - I just need the parts to display in a
tree-view hierarchial fashion. My basic problem is that I end up with
duplicate key value under different roots which the treeview does not
allow.


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) *

 

[Stefan Hoffmann]

hi,

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

As Tom pointed out, you need a unique key. Either use [ParentID] &
[ChildID] (seems sufficient to me) or better, use surrogate IDs
(autoincrement) in your tables.


mfG
--> stefan <--

 

[ccg]

hi,

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

As Tom pointed out, you need a unique key. Either use [ParentID] &
[ChildID] (seems sufficient to me) or better, use surrogate IDs
(autoincrement)   in your tables.

mfG
--> stefan <--

Hi Stefan & Tom:

Thanks so much for responding. I will attempt to use your suggestions.

Part # is a unique field; and the combination of Parent-Part # and
Child-Part# are unique as well. However the same Part # can be used in
several sub-assemblies, which themselves can be part of other sub-
assemblies.

For example:
Car

Window
Bolt
Nut

Door
Window
Nut

Tire
Nut

Engine
Piston
Nut

So <Window> has a sub-assembly of <Bolt and Nut>, but <Window> is also
part of the <Door> assembly also. In addition <Nut> is used in several
sub-assemblies.

If a User chooses only <Window> to display, the TreeView works fine.
However when <Car> is selected I get the Unique key error.

Any code-modifications you can recommend are greatly appreciated.

Thanks,
GC

 

[Stefan Hoffmann]

hi,

Car
Window
Bolt
Nut
Door
Window
Nut

If a User chooses only <Window> to display, the TreeView works fine.
However when <Car> is selected I get the Unique key error.

In this case you need to use a kind of path as key, e.g.
"Car-Window-Nut" and "Car-Door-Nut" or the ids of these levels.


mfG
--> stefan <--

 

[Jack Leach]

I ran into some similar unique-key problems working with a BOM a few months
ago. Some members here suggested a master inventory table with an autonumber
PK (or an SKU number) each item.

So you would see a layout somewhere along these lines:

Window - 456
Nut - 112
Door - 9684
Bolt - 121

Each item in the inventory has it's own ID, regardless of how many subassy
parts go to it (these are handled as a detail of the assy ID).

This worked out well because it kept me from having any issues with trying
to contencate fields to come up with a unique name. Each assembly might have
other assembly IDs as children, but every part will have it's own ID
regardless.

I've never worked with a treeview, but that type of data structure I would
think would help you obtain the unique values you apparently require without
any chance of duplicates.

Unfortunately, this might facilitate a complete redesign of the data
structure.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

hi,

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

As Tom pointed out, you need a unique key. Either use [ParentID] &
[ChildID] (seems sufficient to me) or better, use surrogate IDs
(autoincrement) in your tables.

mfG
--> stefan <--

Hi Stefan & Tom:

Thanks so much for responding. I will attempt to use your suggestions.

Part # is a unique field; and the combination of Parent-Part # and
Child-Part# are unique as well. However the same Part # can be used in
several sub-assemblies, which themselves can be part of other sub-
assemblies.

For example:
Car

Window
Bolt
Nut

Door
Window
Nut

Tire
Nut

Engine
Piston
Nut

So <Window> has a sub-assembly of <Bolt and Nut>, but <Window> is also
part of the <Door> assembly also. In addition <Nut> is used in several
sub-assemblies.

If a User chooses only <Window> to display, the TreeView works fine.
However when <Car> is selected I get the Unique key error.

Any code-modifications you can recommend are greatly appreciated.

Thanks,
GC

 

[ccg]

I ran into some similar unique-key problems working with a BOM a few months
ago.  Some members here suggested a master inventory table with an autonumber
PK (or an SKU number) each item.

So you would see a layout somewhere along these lines:

Window - 456
Nut - 112
Door - 9684
Bolt - 121

Each item in the inventory has it's own ID, regardless of how many subassy
parts go to it (these are handled as a detail of the assy ID).

This worked out well because it kept me from having any issues with trying
to contencate fields to come up with a unique name.  Each assembly might have
other assembly IDs as children, but every part will have it's own ID
regardless.

I've never worked with atreeview, but that type of data structure I would
think would help you obtain the unique values you apparently require without
any chance of duplicates.

Unfortunately, this might facilitate a complete redesign of the data
structure.

hth

--
Jack Leachwww.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."  
-Thomas Edison (1847-1931)

hi,
ccg wrote:
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
As Tom pointed out, you need a unique key. Either use [ParentID] &
[ChildID] (seems sufficient to me) or better, use surrogate IDs
(autoincrement)   in your tables.
mfG
--> stefan <--

Hi Stefan & Tom:

Thanks so much for responding. I will attempt to use your suggestions.

Part # is a unique field; and the combination of Parent-Part # and
Child-Part# are unique as well. However the same Part # can be used in
several sub-assemblies, which themselves can be part of other sub-
assemblies.

For example:
Car

   Window
     Bolt
     Nut

   Door
     Window
     Nut

   Tire
     Nut

    Engine
      Piston
        Nut

So <Window> has a sub-assembly of <Bolt and Nut>, but <Window> is also
part of the <Door> assembly also. In addition <Nut> is used in several
sub-assemblies.

If a User chooses only <Window> to display, theTreeViewworks fine.
However when <Car> is selected I get the Unique key error.

Any code-modifications you can recommend are greatly appreciated.

Thanks,
GC- Hide quoted text -

- Show quoted text -

Hi Jack:

Actually my db-structure is in the format you mentioned, with each
Part# and each Sub-Assembly# having a unique identifier. Sub-
Assemblies can go down several levels (maybe 15) and use other sub-
assemblies.

The Treeview control does not permit duplicate keys anywhere at the
same level; even if it is in a different-branch. In my example <Nut>
is on Level 3 (Car-WINDOW-Nut) which displays properly; however when
the code gets to display another Level 3 (Car-DOOR-Nut) then I get an
error. The <Nut> on a different sub-tree is causing an error. I cannot
build out beforehand all the different sub-paths. I have Auto-Number
that defines each grouping of the 2 part #'s that are related; but I
don't think I can use that to create the entire TreeView.

Thanks,
GC