treeView key change tracking

[Guest]

Hi All,
I am using a TreeView object to show a Bill of Materials and to output the
views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My key is
created by concatenating "a" to my part key (this is because treeveiw keys
must start with an alpha.) In my case the key is a part and I will likely use
the same screw for example at several levels of the BOM.

I found a way around this problem. In the error trapping I stop error #
35602 and change the first letter to the next letter and send it through
again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the nodParent)
for a part that has children, It crashes. It does so because when building
the children, it is trying to attach them to a nodPartent field that starts
with "a" and gets lost. I can not figure out how to capture the nodParent
key when it changes from "a" and feed it back to nodCurrent.

What do you think?


Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String, strDescField
As String, strQty As String, _
Optional varParentBranch As Variant)

On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String, strQ As
String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object

If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)

chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey,
strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True

Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|" &
strDesc & "|" & strQ)
nodCurrent.Expanded = True

End If

bk = rs.Bookmark

AddBranch rs, strPointerField, strIDField, strTextField, strDescField,
strQty, rs(strIDField)

rs.Bookmark = bk
rs.FindNext strCriteria

Loop

exit_out:
Exit Sub

error_out:

If err.Number = 35602 Then ' This is the error for duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly, "Duplicate
Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) & rs(strIDField)
Resume chgDupKey
Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If

Resume exit_out

End Sub

 

[David C. Holley]

Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand all
of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.

 

[Guest]

David, thanks for the help. I've prepard an eMail, what is your eMail Address?

 

[David C. Holley]

(e-mail address removed)

David, thanks for the help. I've prepard an eMail, what is your eMail Address?

:

 

[David C. Holley]

I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

 

[David C. Holley]

Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:

 

[Guest]

Thanks David,

Since my last posting my laptop died. Your response makes sense, and I
think I'm on the right track. What I failed to mention is that I'm a novice
with code. Some of it I can follow easily, however in this (TreeView) I'm
completely at a loss.

I can follow the code and see what it is doing but what I don't understand
is how the bk keeps advancing down the list and then returns to the top. I
think this is why I can not figure out how to capture the prefix that has
been assigned at any particular level. (becuase it is a recursive recordset,
I can not assign levels ie. acct, master, invoice)

Recordset
Prnt Chld
11
11 12
12 2

This is want the tree should look like if were behaving properly:

a11
a12
a2
b12
b2

But instead I get:

a11
a12
a2
b12
* err 91 Obj variable or With Block var net set

There are two lines where I set the Prefix:
* Set nodParent = objTree.Nodes("a" & varParentBranch)
* strKey = "a" & rs(strIDField)

When I step through them, if I change both to "b" after b12 is added, it
will add b2 as desired.

If I don't when it comes through to add 2 the second time, error 91 trips
when it gets to this line:

Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText &
"|" & strDesc & "|" & strQ)

Any suggestions how I can store the prefix for an item when adding a child
to it?

Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:


Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand
all of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.



aWs wrote:

Hi All,
I am using a TreeView object to show a Bill of Materials and to
output the views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My
key is created by concatenating "a" to my part key (this is because
treeveiw keys must start with an alpha.) In my case the key is a
part and I will likely use the same screw for example at several
levels of the BOM.

I found a way around this problem. In the error trapping I stop
error # 35602 and change the first letter to the next letter and
send it through again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the
nodParent) for a part that has children, It crashes. It does so
because when building the children, it is trying to attach them to a
nodPartent field that starts with "a" and gets lost. I can not
figure out how to capture the nodParent key when it changes from "a"
and feed it back to nodCurrent.

What do you think?

Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String,
strDescField As String, strQty As String, _
Optional varParentBranch As Variant)
On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String,
strQ As String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object
If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)
chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild,
strKey, strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True
Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|"
& strDesc & "|" & strQ)
nodCurrent.Expanded = True
End If

bk = rs.Bookmark
AddBranch rs, strPointerField, strIDField, strTextField,
strDescField, strQty, rs(strIDField)
rs.Bookmark = bk
rs.FindNext strCriteria
Loop
exit_out:
Exit Sub
error_out:
If err.Number = 35602 Then ' This is the error for
duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly,
"Duplicate Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) &
rs(strIDField)
Resume chgDupKey Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If
Resume exit_out
End Sub

 

[David C. Holley]

Any suggestions how I can store the prefix for an item when adding a
child to it?

If you need the key for a parent node, just use the .PARENT.KEY property
of the node that you've created as in

nodCurrent.parent.key (after its been added)

From there use the LEFT() or MID() statements to grab the prefix.

Help?

Thanks David,

Since my last posting my laptop died. Your response makes sense, and I
think I'm on the right track. What I failed to mention is that I'm a novice
with code. Some of it I can follow easily, however in this (TreeView) I'm
completely at a loss.

I can follow the code and see what it is doing but what I don't understand
is how the bk keeps advancing down the list and then returns to the top. I
think this is why I can not figure out how to capture the prefix that has
been assigned at any particular level. (becuase it is a recursive recordset,
I can not assign levels ie. acct, master, invoice)

Recordset
Prnt Chld
11
11 12
12 2

This is want the tree should look like if were behaving properly:

a11
a12
a2
b12
b2

But instead I get:

a11
a12
a2
b12
* err 91 Obj variable or With Block var net set

There are two lines where I set the Prefix:
* Set nodParent = objTree.Nodes("a" & varParentBranch)
* strKey = "a" & rs(strIDField)

When I step through them, if I change both to "b" after b12 is added, it
will add b2 as desired.

If I don't when it comes through to add 2 the second time, error 91 trips
when it gets to this line:

Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText &
"|" & strDesc & "|" & strQ)

Any suggestions how I can store the prefix for an item when adding a child
to it?

:

Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

aWs wrote:


David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:



Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand
all of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.



aWs wrote:


Hi All,
I am using a TreeView object to show a Bill of Materials and to
output the views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My
key is created by concatenating "a" to my part key (this is because
treeveiw keys must start with an alpha.) In my case the key is a
part and I will likely use the same screw for example at several
levels of the BOM.

I found a way around this problem. In the error trapping I stop
error # 35602 and change the first letter to the next letter and
send it through again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the
nodParent) for a part that has children, It crashes. It does so
because when building the children, it is trying to attach them to a
nodPartent field that starts with "a" and gets lost. I can not
figure out how to capture the nodParent key when it changes from "a"
and feed it back to nodCurrent.

What do you think?

Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String,
strDescField As String, strQty As String, _
Optional varParentBranch As Variant)
On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String,
strQ As String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object
If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)
chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild,
strKey, strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True
Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|"
& strDesc & "|" & strQ)
nodCurrent.Expanded = True
End If

bk = rs.Bookmark
AddBranch rs, strPointerField, strIDField, strTextField,
strDescField, strQty, rs(strIDField)
rs.Bookmark = bk
rs.FindNext strCriteria
Loop
exit_out:
Exit Sub
error_out:
If err.Number = 35602 Then ' This is the error for
duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly,
"Duplicate Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) &
rs(strIDField)
Resume chgDupKey Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If
Resume exit_out
End Sub

 

[Guest]

Thank You!

It works. My tree will now allow me to duplicate children under duplicate
parents.

It is a thing of beauty!

Any suggestions how I can store the prefix for an item when adding a

child to it?

If you need the key for a parent node, just use the .PARENT.KEY property
of the node that you've created as in

nodCurrent.parent.key (after its been added)

From there use the LEFT() or MID() statements to grab the prefix.

Help?

Thanks David,

Since my last posting my laptop died. Your response makes sense, and I
think I'm on the right track. What I failed to mention is that I'm a novice
with code. Some of it I can follow easily, however in this (TreeView) I'm
completely at a loss.

I can follow the code and see what it is doing but what I don't understand
is how the bk keeps advancing down the list and then returns to the top. I
think this is why I can not figure out how to capture the prefix that has
been assigned at any particular level. (becuase it is a recursive recordset,
I can not assign levels ie. acct, master, invoice)

Recordset
Prnt Chld
11
11 12
12 2

This is want the tree should look like if were behaving properly:

a11
a12
a2
b12
b2

But instead I get:

a11
a12
a2
b12
* err 91 Obj variable or With Block var net set

There are two lines where I set the Prefix:
* Set nodParent = objTree.Nodes("a" & varParentBranch)
* strKey = "a" & rs(strIDField)

When I step through them, if I change both to "b" after b12 is added, it
will add b2 as desired.

If I don't when it comes through to add 2 the second time, error 91 trips
when it gets to this line:

Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText &
"|" & strDesc & "|" & strQ)

Any suggestions how I can store the prefix for an item when adding a child
to it?

:

Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

David C. Holley wrote:

I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

aWs wrote:


David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:



Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand
all of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.



aWs wrote:


Hi All,
I am using a TreeView object to show a Bill of Materials and to
output the views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My
key is created by concatenating "a" to my part key (this is because
treeveiw keys must start with an alpha.) In my case the key is a
part and I will likely use the same screw for example at several
levels of the BOM.

I found a way around this problem. In the error trapping I stop
error # 35602 and change the first letter to the next letter and
send it through again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the
nodParent) for a part that has children, It crashes. It does so
because when building the children, it is trying to attach them to a
nodPartent field that starts with "a" and gets lost. I can not
figure out how to capture the nodParent key when it changes from "a"
and feed it back to nodCurrent.

What do you think?

Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String,
strDescField As String, strQty As String, _
Optional varParentBranch As Variant)
On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String,
strQ As String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object
If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)
chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild,
strKey, strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True
Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|"
& strDesc & "|" & strQ)
nodCurrent.Expanded = True
End If

bk = rs.Bookmark
AddBranch rs, strPointerField, strIDField, strTextField,
strDescField, strQty, rs(strIDField)
rs.Bookmark = bk
rs.FindNext strCriteria
Loop
exit_out:
Exit Sub
error_out:
If err.Number = 35602 Then ' This is the error for
duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly,
"Duplicate Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) &
rs(strIDField)
Resume chgDupKey Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If
Resume exit_out
End Sub

 

[David C. Holley]

OH YEAH BABY! Now do you want the ability to execute certain actions on
a parent node (ex: Print Work Order, Email Work Order, Add Part)

Thank You!

It works. My tree will now allow me to duplicate children under duplicate
parents.

It is a thing of beauty!

:

Any suggestions how I can store the prefix for an item when adding a

child to it?

If you need the key for a parent node, just use the .PARENT.KEY property
of the node that you've created as in

nodCurrent.parent.key (after its been added)

From there use the LEFT() or MID() statements to grab the prefix.

Help?

Thanks David,

Since my last posting my laptop died. Your response makes sense, and I
think I'm on the right track. What I failed to mention is that I'm a novice
with code. Some of it I can follow easily, however in this (TreeView) I'm
completely at a loss.

I can follow the code and see what it is doing but what I don't understand
is how the bk keeps advancing down the list and then returns to the top. I
think this is why I can not figure out how to capture the prefix that has
been assigned at any particular level. (becuase it is a recursive recordset,
I can not assign levels ie. acct, master, invoice)

Recordset
Prnt Chld
11
11 12
12 2

This is want the tree should look like if were behaving properly:

a11
a12
a2
b12
b2

But instead I get:

a11
a12
a2
b12
* err 91 Obj variable or With Block var net set

There are two lines where I set the Prefix:
* Set nodParent = objTree.Nodes("a" & varParentBranch)
* strKey = "a" & rs(strIDField)

When I step through them, if I change both to "b" after b12 is added, it
will add b2 as desired.

If I don't when it comes through to add 2 the second time, error 91 trips
when it gets to this line:

Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText &
"|" & strDesc & "|" & strQ)

Any suggestions how I can store the prefix for an item when adding a child
to it?

:



Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

David C. Holley wrote:


I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

aWs wrote:



David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:




Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand
all of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.



aWs wrote:



Hi All,
I am using a TreeView object to show a Bill of Materials and to
output the views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My
key is created by concatenating "a" to my part key (this is because
treeveiw keys must start with an alpha.) In my case the key is a
part and I will likely use the same screw for example at several
levels of the BOM.

I found a way around this problem. In the error trapping I stop
error # 35602 and change the first letter to the next letter and
send it through again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the
nodParent) for a part that has children, It crashes. It does so
because when building the children, it is trying to attach them to a
nodPartent field that starts with "a" and gets lost. I can not
figure out how to capture the nodParent key when it changes from "a"
and feed it back to nodCurrent.

What do you think?

Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String,
strDescField As String, strQty As String, _
Optional varParentBranch As Variant)
On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String,
strQ As String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object
If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)
chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild,
strKey, strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True
Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|"
& strDesc & "|" & strQ)
nodCurrent.Expanded = True
End If

bk = rs.Bookmark
AddBranch rs, strPointerField, strIDField, strTextField,
strDescField, strQty, rs(strIDField)
rs.Bookmark = bk
rs.FindNext strCriteria
Loop
exit_out:
Exit Sub
error_out:
If err.Number = 35602 Then ' This is the error for
duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly,
"Duplicate Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) &
rs(strIDField)
Resume chgDupKey Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If
Resume exit_out
End Sub

 

[Guest]

YES! I don't want to stop! I can't stop!

How do you print, email, or open a form? Are these things easily done?

OH YEAH BABY! Now do you want the ability to execute certain actions on
a parent node (ex: Print Work Order, Email Work Order, Add Part)

Thank You!

It works. My tree will now allow me to duplicate children under duplicate
parents.

It is a thing of beauty!

:

Any suggestions how I can store the prefix for an item when adding a
child to it?

If you need the key for a parent node, just use the .PARENT.KEY property
of the node that you've created as in

nodCurrent.parent.key (after its been added)

From there use the LEFT() or MID() statements to grab the prefix.

Help?

aWs wrote:

Thanks David,

Since my last posting my laptop died. Your response makes sense, and I
think I'm on the right track. What I failed to mention is that I'm a novice
with code. Some of it I can follow easily, however in this (TreeView) I'm
completely at a loss.

I can follow the code and see what it is doing but what I don't understand
is how the bk keeps advancing down the list and then returns to the top. I
think this is why I can not figure out how to capture the prefix that has
been assigned at any particular level. (becuase it is a recursive recordset,
I can not assign levels ie. acct, master, invoice)

Recordset
Prnt Chld
11
11 12
12 2

This is want the tree should look like if were behaving properly:

a11
a12
a2
b12
b2

But instead I get:

a11
a12
a2
b12
* err 91 Obj variable or With Block var net set

There are two lines where I set the Prefix:
* Set nodParent = objTree.Nodes("a" & varParentBranch)
* strKey = "a" & rs(strIDField)

When I step through them, if I change both to "b" after b12 is added, it
will add b2 as desired.

If I don't when it comes through to add 2 the second time, error 91 trips
when it gets to this line:

Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText &
"|" & strDesc & "|" & strQ)

Any suggestions how I can store the prefix for an item when adding a child
to it?

:



Didn't show this but the following nodes exist under each CLIENT or MASTER

Add New Invoice
Archive

The keys vary though in that I use the recordID at the end so
05AI:24 represents the node that add an invoice for the account with the
recordID of 24
05AI:4 does the same, but for the account with the recordID of 4

I use the value of the node.parent.key property to determine if I'm
working with a CLIENT (09CLIENT:24)or MASTER ACCOUNT (09MASTER:4).

David C. Holley wrote:


I think that this will work for you. In my TreeView, I use a prefix
before each and every value that makes up the key. So all of the keys
appear in the format [PREFIX][ACTUAL KEY]. The prefix indicates the
length of the prefix and describes the identity of the node. My
implementation displays client and master account nodes, invoices
attached to each and nodes that execute various actions as in....

TREE VIEW KEY
MENU OPTIONS 06N/A:MenuOptions
Find Transfer 08NOACT:FindTransport
2004 Transfers 07FIND:2004
2005 Transfers 07FIND:2005
ACCOUNT INVOICING 06N/A:AccountInvoicing
Clients 11Category:Clients
Find Client Account 07MENU:FindClientAccount
David Holley 09CLIENT:24
Master Accounts 11Category:MasterAccounts
Find Master Account 07MENU:FindMasterAccount
AAA Travel 09MASTER:24
Add New Invoice 05AI:24
Post Settlement 17PostSettlement:24
Archive 10ARCHIVE:24
John Smith & Family 10INVOICE:2342352
Staff Transfers - Mark 10INVOICE:3453422
Invoicing Test 09MASTER:54

The first two characters of each key represent the length of the prefix.
I have a function that uses this value to remove the prefix from the
nodeKey to provide the value that I need. The value the follows the
colon [:]. I did this to because it was possible that a CLIENT and a
MASTER ACCOUNT could have the same key. (They exist in different tables
and so a duplicate in treeView can exist.) By prefixing the nodes with
CLIENT and MASTER I was able to differentiate between the two since the
nodeKeys are technically different.

From looking at the email that you sent, it appears that using a prefix
will work for. Think in terms of how the information is structured in
the database and go from there. It was a bit difficult to figure out the
DB structure, but I think that it will work. Remember that the NODE.TEXT
is just for display ONLY. The NODE.KEY is what matters. Althought the
DESCRIPTION might be "CASE LOWER" each record in the child table has a
different PRIMARY KEY which you would use to build the NODE KEY'S.

Make sense?

aWs wrote:



David, thanks for the help. I've prepard an eMail, what is your eMail
Address?

:




Can you post a sample heiracrhy of the treeView or email me a screen
shot? I have a fix that should work, but I need to better understand
all of the nodes and their relationship to one another.

One item for now - the key does not have to be a alpha character, a
number WILL work.



aWs wrote:



Hi All,
I am using a TreeView object to show a Bill of Materials and to
output the views path to excel for analysis.
TreeView does not allow you to use the same key twice in a veiw. My
key is created by concatenating "a" to my part key (this is because
treeveiw keys must start with an alpha.) In my case the key is a
part and I will likely use the same screw for example at several
levels of the BOM.

I found a way around this problem. In the error trapping I stop
error # 35602 and change the first letter to the next letter and
send it through again until it is unique.

Here is my problem. When the key changes from "a" to "b" (in the
nodParent) for a part that has children, It crashes. It does so
because when building the children, it is trying to attach them to a
nodPartent field that starts with "a" and gets lost. I can not
figure out how to capture the nodParent key when it changes from "a"
and feed it back to nodCurrent.

What do you think?

Sub AddBranch(rs As Recordset, strPointerField As String, _
strIDField As String, strTextField As String,
strDescField As String, strQty As String, _
Optional varParentBranch As Variant)
On Error GoTo error_out

Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strDesc As String,
strQ As String, strKey As String, strOH As String
Dim nodParent As Node, bk As String
Dim i As Integer

Set objTree = Me!xTree.Object
If IsMissing(varParentBranch) Then
strCriteria = strPointerField & " Is Null"
Else
strCriteria = BuildCriteria(strPointerField, _
rs.Fields(strPointerField).Type, "=" & varParentBranch)
Set nodParent = objTree.Nodes("a" & varParentBranch)
End If

rs.FindFirst strCriteria

Do Until rs.NoMatch

strText = rs(strTextField)
strDesc = rs(strDescField)
strQ = rs(strQty)
strKey = "a" & rs(strIDField)
chgDupKey:
If Not IsMissing(varParentBranch) Then
Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild,
strKey, strText & "|" & strDesc & "|" & strQ)
nodCurrent.Expanded = True
Else
Set nodCurrent = objTree.Nodes.Add(, , strKey, strText & "|"
& strDesc & "|" & strQ)
nodCurrent.Expanded = True
End If

bk = rs.Bookmark
AddBranch rs, strPointerField, strIDField, strTextField,
strDescField, strQty, rs(strIDField)
rs.Bookmark = bk
rs.FindNext strCriteria
Loop
exit_out:
Exit Sub
error_out:
If err.Number = 35602 Then ' This is the error for
duplicate key present
MsgBox "Duplicate Part, attempting to change key: " & strKey & _
Chr(13) + Chr(10) & nodCurrent.FullPath, vbOKOnly,
"Duplicate Part"
strKey = Chr(Asc(Left(strKey, 1)) + 1) &
rs(strIDField)
Resume chgDupKey Else
MsgBox "Error Number " & err.Number & Chr(13) + Chr$(10) &
err.Description
End If
Resume exit_out
End Sub