[RESOLVED] Loading GrandChildNodes from DB

I had a lot of help here to get this sorted out with Parent and child nodes.

I am now after loading GrandChildNodes to the tree. These will be the actual files, when clicked on, that open in a RTB
It would be straight forward, but some of the Parent Nodes have Child Nodes, so to add a GrandChild Node, I could probably work out.
But Some of the Parent Nodes don't have Child Nodes.

I have attached a Pic of my DB Layout and also the treeview in my App.

How do I code to add a GrandChild node to all parent nodes?

As you will see in the TreeView pic, VBA has two child nodes, where as APi has no child node.

Here is my code so far:

vb Code:

'==============================================================================================
'Load treeview nodes from DB
'Load arrays holding the names of the 'Parents' and the 'Children' nodes
'Also load 'NodesKey' array to use later to open the TV with the option saved in the Registry
'==============================================================================================
 
With tvwCode
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("tvwNodes").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("tvwNodes").Value
            'add 1 to the key
            lngNodeParent = lngNodeParent + 1
            'update key number if previous Parent node had children
            If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1
            'add the new node, with the new key and it's name
            .Nodes.Add , , "P" & CStr(lngNodeParent), strNodeParent, "Folder Closed"
            If PCtr = 0 Then
                strParent(PCtr) = strNodeParent
                NodesKey(KeyCtr) = strNodeParent & " P" & CStr(lngNodeParent)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            Else
                ReDim Preserve strParent(UBound(strParent) + 1)
                strParent(PCtr) = strNodeParent
                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                NodesKey(KeyCtr) = strNodeParent & " P" & CStr(lngNodeParent)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            End If
        End If
 
        'if needed, add the child
        'check if there are any child nodes -
        'if Fields is "" then no new child nodes
        'move on to next parent node
        If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
            'if Fields is not empty - add new child node
            strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                    'add 1 to the key
                    lngNodeChild = lngNodeChild + 1
                    'if necessary add 1 to make number unique
                    If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1
                    'add the new child node with the path of the parent node
                    .Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild, "Folder Closed"
                    If CCtr = 0 Then
                        strChild(CCtr) = strNodeParent & " " & strNodeChild
                        If KeyCtr > 0 Then
                            ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                            NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                            KeyCtr = KeyCtr + 1
                        Else
                            NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                            KeyCtr = KeyCtr + 1
                        End If
                        CCtr = CCtr + 1
                    Else
                        ReDim Preserve strChild(UBound(strChild) + 1)
                        strChild(CCtr) = strNodeParent & " " & strNodeChild
                        ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                        NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                        CCtr = CCtr + 1
                        KeyCtr = KeyCtr + 1
                    End If
                End If
        End If

Ok, slowly, but surely I am getting there :lol:

I have added the following code, and I am almost there:

vb Code:

If strNodeGrandChild <> rs.Fields("tvwGrandChildNodes").Value & "" Then
            'if Fields is not empty - add new grandchild node
            strNodeGrandChild = rs.Fields("tvwGrandChildNodes").Value & ""
            .Nodes.Add strNodeParent, strNodeChild, , strNodeGrandChild, "Leaf"
        End If

strNodeParent = VB Code
strNodeChild = Functions
strNodeGrandChild = Change Text Case

So all values are as expected, but I am getting the error:
Element not found
on the .Nodes.Add line.

I have tried using tvwLast, tvwNext
but these don't work, or at least not the way I am using them.:cool:

I know this is the problem but I'm not sure what to change.

Right, with this code, added after the Childnodes are added, I am now getting what is shown in the image, but I am now stumped. :cry:

There should only be 2 entries, not a third empty one, and they are supposed to be the next level in!

vb Code:

'if no Child node, is there a grandchild node
        If strNodeChild = "" Then
            If strNodeGrandChild <> rs.Fields("tvwGrandChildNodes").Value & "" Then
                'if Fields is not empty - add new grandchild node
                strNodeGrandChild = rs.Fields("tvwGrandChildNodes").Value & ""
                .Nodes.Add "P" & CStr(lngNodeParent), tvwLast, , strNodeGrandChild, "Leaf"
            End If
        Else
            If strNodeGrandChild <> rs.Fields("tvwGrandChildNodes").Value & "" Then
                'if Fields is not empty - add new grandchild node
                strNodeGrandChild = rs.Fields("tvwGrandChildNodes").Value & ""
                .Nodes.Add "C" & CStr(lngNodeChild), tvwLast, , strNodeGrandChild, "Leaf"
            End If
        End If

You are currently adding to the Parent ("P" & CStr(lngNodeParent)) in some cases, but should always be adding to the Child ("C" & CStr(lngNodeChild)).

You should also be checking that rs.Fields("tvwGrandChildNodes").Value is not blank

Thanks si

Quote:

---

Originally Posted by si_the_geek

You are currently adding to the Parent ("P" & CStr(lngNodeParent)) in some cases, but should always be adding to the Child ("C" & CStr(lngNodeChild)).

---

Sorry here si, I think I am missing something:confused: Now that may surprise you :lol:

If the parent has no child, I though I would be adding it to a parent, where as if it has a child, I though I would be adding it to that child.

Quote:

---

Originally Posted by si_the_geek

You should also be checking that rs.Fields("tvwGrandChildNodes").Value is not blank

---

I though that what you showed me in an earlier post, by adding the [& " "] made sure it wasn't blank, but an empty string.

Or am I missing something else again.:eek:

It's late now, so I think I will come back to this tomorrow.

Quote:

---

Originally Posted by aikidokid

If the parent has no child, I though I would be adding it to a parent, where as if it has a child, I though I would be adding it to that child.

---

Nope.. the grandchild is a "child" of an existing child. You cannot have a gap between the parent and their grandchild.

I'm pretty sure you should be using tvwChild instead of tvwLast

Quote:

---

I though that what you showed me in an earlier post, by adding the [& " "] made sure it wasn't blank, but an empty string.

---

That is for treating Null's as if they are empty strings.. but ignore that bit of my post, as you already have the If statement you need!

Quote:

---

Originally Posted by si_the_geek

Nope.. the grandchild is a "child" of an existing child. You cannot have a gap between the parent and their grandchild.

---

Makes sense now. :)

Quote:

---

Originally Posted by si

I'm pretty sure you should be using tvwChild instead of tvwLast

---

I am now, thanks, and it's working.


OK, I have it working now with the following code:

vb Code:

If Not IsNull(rs.Fields("tvwGrandChildNodes")) Then
            If strNodeGrandChild <> rs.Fields("tvwGrandChildNodes").Value & "" Then
                            
                Debug.Print rs.Fields("tvwGrandChildNodes").Value & ""
                'if Fields is not empty - add new grandchild node
                strNodeGrandChild = rs.Fields("tvwGrandChildNodes").Value & ""
                .Nodes.Add "C" & CStr(lngNodeChild), tvwChild, , strNodeGrandChild, "Leaf"
            End If
        End If

But, and there's always a but ....

When you look in the DB_Fields.gif in Post#1, you can see the category API, Modules have no entries in the tvwChildNode field.

So, when loading the Nodes, when I get to the part that checks to see if there are any entries in the tvwChildNodes field, would I then have to check if there was anything in the tvwGrandChildNode field and then add it before continuing with the loop to load the rest of the nodes.

OR, will I have to save it differently? If so, how do I do this without creating an extra unnecessary level of nodes?

Hope this makes some sort of sense? :lol:

:cry: Why is nothing sraight forward in programming:afrog:

I have resolved this, although not 100% the way I would have liked to.

As I know in advance what the categories are going to be, I have added a 'Select Case' statement to filter out the categories without the child node, then loaded the nodes this way.

It doesn't really make sense to check the GrandChild if there is no Child, so the code could go between lines 67 and 68 of your original post.

This does make it a bit more awkward in terms of adding Nodes, but to be honest I think you should deal with the actual Items (what you currently have in the GrandChild field) slightly differently. We should think about this tho, based on your current table design.

What I think you should have in this table is just the Nodes, and the actual items (including their titles) stored in a separate table. An extra Number field would then be added to that table (called NodeID?), which specifies which 'folder' the item goes in - it would link to the AutoNumber field in this table, so "RemoveAmp" and "Change Text Case" would have a NodeID value of 14.

If you do that, the ID's for the nodes would change from "P" & CStr(lngNodeParent) and "C" & CStr(lngNodeChild) to "N" & rs.Fields("AutoNumber").Value , and you would use a separate loop to add the items to the existing nodes.

Doing this would be a better design, as it has less repeating data - but it could be confusing for you!


The alternative is to keep what you've got, and after your existing new bit (between 67 and 68) add an Else clause (at the same level as the End If on line 68) with basically the same bit of code again - but this time adding to the Parent node instead of the Child.


edit: your new idea isn't a great one.. if there is ever any change in the data (such as a change of spelling of a node), you will need to modify (and re-compile) the program, instead of just changing data in the database.

Quote:

---

Why is nothing straight forward in programming

---

It is if you look at a small enough level.. the bigger picture gets easily complicated if you don't plan it in advance. Unfortunately I haven't helped there, I seem to have made things a bit worse! :blush:

First thinks first ....

Quote:

---

Originally Posted by si_the_geek

It is if you look at a small enough level.. the bigger picture gets easily complicated if you don't plan it in advance. Unfortunately I haven't helped there, I seem to have made things a bit worse! :blush:

---

Far from it si, I am here to learn. My problem is I am just a bit too impatient. ;)
I have been doing programming in my own time, and learning with the help of this forum mainly, on and off for ages, and as of yet, I have yet to compile a program I have written.
This one is the closest I have come and that is what is making me impatient.

As for the DB, I can redesign this anyway needed. :thumb:
The code I have saved in it, is saved as text files, which is what I did originally.

It's just as long as you guys are patient enough for me to catch up with what you are thinking ;) (which everybody has been so far)

Quote:

---

Originally Posted by si_the_geek

What I think you should have in this table is just the Nodes, and the actual items (including their titles) stored in a separate table. An extra Number field would then be added to that table (called NodeID?), which specifies which 'folder' the item goes in - it would link to the AutoNumber field in this table, so "RemoveAmp" and "Change Text Case" would have a NodeID value of 14.

If you do that, the ID's for the nodes would change from "P" & CStr(lngNodeParent) and "C" & CStr(lngNodeChild) to "N" & rs.Fields("AutoNumber").Value , and you would use a separate loop to add the items to the existing nodes.

Doing this would be a better design, as it has less repeating data - but it could be confusing for you!

---

This just shows how much I have to learn.:)
I didn't think that the design of the DB would be so important, and also that it should be sorted out first!

Quote:

---

Originally Posted by si_the_geek

edit: your new idea isn't a great one.. if there is ever any change in the data (such as a change of spelling of a node), you will need to modify (and re-compile) the program, instead of just changing data in the database.

---

I sort of new this :blush:

I think I will go with the re-designing of the DB and go from their.

I will get a couple of pics of the two tables I have so far and post them, and if you wouldn't mind, get some advice on how to change them for the better. :D

OK, I have attached the two images of the two tables I have at present in my DB
Obviously, tbl_Nodes stores the names of the nodes, the grandchild nodes being the actual files to open (Well for most of them anyway :rolleyes: )
tbl_Code is where the actual file is stored.
:) After the comments in another thread, I may wall use this re-design to not use the name Code for the field, maybe fldCode or similar.

vb Code:

This is the code I ended up using, when I hard coded it :o :
With tvwCode
    .Nodes.Add , , "Source", "Source Code", "Folder Closed"
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("tvwNodes").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("tvwNodes").Value
            'add 1 to the key
            lngNodeParent = lngNodeParent + 1
            'update key number if previous Parent node had children
            If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1
            'add the new node, with the new key and it's name
            .Nodes.Add "Source", tvwChild, "P" & CStr(lngNodeParent), strNodeParent, "Folder Closed"
            If PCtr = 0 Then
                strParent(PCtr) = strNodeParent
                NodesKey(KeyCtr) = strNodeParent & " P" & CStr(lngNodeParent)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            Else
                ReDim Preserve strParent(UBound(strParent) + 1)
                strParent(PCtr) = strNodeParent
                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                NodesKey(KeyCtr) = strNodeParent & " P" & CStr(lngNodeParent)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            End If
        End If
 
        'if needed, add the child
        'check if there are any child nodes -
        'if Fields is "" then no new child nodes
        'move on to next parent node
        Select Case rs!tvwnodes
            Case Is = "API", "Modules", "SQL"
                If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
                    'if Fields is not empty - add new child node
                    strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                        If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                            'add 1 to the key
                            lngNodeChild = lngNodeChild + 1
                            'if necessary add 1 to make number unique
                            If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1
                            'add the new child node with the path of the parent node
                            .Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild, "Leaf"
                            If CCtr = 0 Then
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                If KeyCtr > 0 Then
                                    ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                    KeyCtr = KeyCtr + 1
                                Else
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                    KeyCtr = KeyCtr + 1
                                End If
                                CCtr = CCtr + 1
                            Else
                                ReDim Preserve strChild(UBound(strChild) + 1)
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                CCtr = CCtr + 1
                                KeyCtr = KeyCtr + 1
                            End If
                        End If
                End If
            Case Else
                If strNodeChild <> rs.Fields("tvwChildNodes").Value & "" Then
                    'if Fields is not empty - add new child node
                    strNodeChild = rs.Fields("tvwChildNodes").Value & ""
                        If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                            'add 1 to the key
                            lngNodeChild = lngNodeChild + 1
                            'if necessary add 1 to make number unique
                            If lngNodeChild <= lngNodeParent Then lngNodeChild = lngNodeParent + 1
                            'add the new child node with the path of the parent node
                            .Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild, "Folder Closed"
                            If CCtr = 0 Then
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                If KeyCtr > 0 Then
                                    ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                    KeyCtr = KeyCtr + 1
                                Else
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                    KeyCtr = KeyCtr + 1
                                End If
                                CCtr = CCtr + 1
                            Else
                                ReDim Preserve strChild(UBound(strChild) + 1)
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & " " & "C" & CStr(lngNodeChild)
                                CCtr = CCtr + 1
                                KeyCtr = KeyCtr + 1
                            End If
                        End If
                End If
            End Select
        
        '(grand-child is similar to child, but you dont need to store the text/key!)
        If Not IsNull(rs.Fields("tvwGrandChildNodes")) Then
            If strNodeGrandChild <> rs.Fields("tvwGrandChildNodes").Value & "" Then
                'if Fields is not empty - add new grandchild node
                strNodeGrandChild = rs.Fields("tvwGrandChildNodes").Value & ""
                .Nodes.Add "C" & CStr(lngNodeChild), tvwChild, , strNodeGrandChild, "Leaf"
            End If
        End If
        rs.MoveNext
    Loop
End With

The field name Code is actually valid, it's just one of those that looks like a reserved word, but it isn't.

In tbl_Code I would have these fields:
Code_ID - the AutoNumber field (I don't see a reason for your current Code_ID data)
Code_Description - as it is
Notes - as it is
Code_Text - same as Code (just to avoid confusion over the field name!)
Node_ID - link to the Nodes table

In tbl_Nodes I would have these fields:
Node_ID - the AutoNumber field
NodeName - same as tvwNodes
ChildNodeName - same as tvwChildNodes
[GrandChildNodeName - if needed, an extra level of 'folders']

In theory (but only in theory!) you would not have a "Child" or "GrandChild" field, but instead have a "ParentID" field link which links back to the same table.. this would mean less data is stored (eg: "VB Code" would only be stored once), but it makes the code (and the database itself) much more complex, so isn't worth the hassle!

I was almost finished this when you posted :D

I have the tables as you suggested.

tbl_Code:
I have set the text and memo default value's to ""
The Node_ID filed set to "number"

tbl_Nodes:
I have set the text default value's to ""
The Node_ID field is set to autonumber

Do I need to change the Indexed to "Yes (No Duplicates) for the Nodes and Code_Description fields?

For the amount of data you have, there isn't much point in having indexes.

If chosen carefully they make searching a 'big' table faster, but are very unlikely to provide any benefit here, as there aren't enough rows of data to make a difference.

By setting it to "No Duplicates" it would mean that you couldn't have two items with the same name.. in tbl_Code this could be a problem, as it is perfectly valid to have two items with the same name, as long as they are under different nodes.

Thanks si,

I am getting to work on changing the code to load the nodes now.
:cry: Just realised how much code else where I am probably going to have to change, in the save code and open code.

Quote:

---

Originally Posted by si_the_geek

If you do that, the ID's for the nodes would change from "P" & CStr(lngNodeParent) and "C" & CStr(lngNodeChild) to "N" & rs.Fields("AutoNumber").Value ,

---

I've got this, I hope it is just that simple.

Quote:

---

Originally Posted by si_the_geek

What I think you should have in this table is just the Nodes

---

So load this first:

Source Code
|____VB Code
| |_____Functions
|____Modules

Quote:

---

Originally Posted by si_the_geek

you would use a separate loop to add the items to the existing nodes.

---

Not quite sure how to do this!

When I add the new data to the DB, it would be something like this?

Nodes_ID in the tbl_Nodes would be as follows:
API = 1
Modules = 2
VB Code Functions = 3
VB Code Subs = 4

So when I add a new piece of code to the DB:

tbl_Code - Node_ID would be the value of which ever node it is stored under, such as:
mRegistry (Module) would have '2' stored in the Node_ID field.

Then loop through the tbl_Code Node_ID field and would you load each entry as you go, or loop looking for any '1' and then any '2' etc?

ALSO
Would you have Modules as one of the Child nodes of VB?
I have also decided to leave the 'Notes' node off of the treeview. It is accessed else where now.

Sorry if it sounds obvious, but it ain't to me :lol:

Another question!

Is the AutoNumber field also the Primary Key as well?

Quote:

---

Originally Posted by aikidokid

When I add the new data to the DB, it would be something like this?
...

---

Exactly. :)

Quote:

---

Then loop through the tbl_Code Node_ID field and would you load each entry as you go, or loop looking for any '1' and then any '2' etc?

---

You would do similar to the current setup you have for adding nodes, except reading from the tbl_Code table, and simply adding every item directly to the node - using .Nodes.Add "N" & rs.Fields("NodeID").Value, tvwChild, ...

Quote:

---

Would you have Modules as one of the Child nodes of VB?

---

It's completely up to you.. it's easy enough to change it later if you want (you just need to edit the Nodes table).

Quote:

---

Is the AutoNumber field also the Primary Key as well?

---

Yep. :)

The NodeID field in tbl_Code is a Foreign Key (links to a Primary Key of another table).

When adding new data to the DB, does the autonumber field automatically increase, you don't have to add to it?

I have changed this as shown:

Quote:

---

Originally Posted by si

If you do that, the ID's for the nodes would change from "P" & CStr(lngNodeParent) and "C" & CStr(lngNodeChild) to "N" & rs.Fields("AutoNumber").Value

---

so would this line

vb Code:

.Nodes.Add "P" & CStr(lngNodeParent), tvwChild, "C" & CStr(lngNodeChild), strNodeChild, "Folder Closed"

now become

vb Code:

.Nodes.Add "N" & rs.Fields("AutoNumber").Value, tvwChild, "N" & rs.Fields("AutoNumber").Value, strNodeChild, "Folder Closed"

or

vb Code:

.Nodes.Add "N" & rs.Fields("AutoNumber").Value, tvwChild,  strNodeChild, "Folder Closed"

Because on this line I am now getting the error:
Item cannot be found in the collection corresponding to the name or ordinal

on this line- this part - "N" & rs.Fields("AutoNumber").Value

vb Code:

With tvwCode
    .Nodes.Add , , "Source", "Source Code", "Folder Closed"
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("NodeName").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("NodeName").Value
            'add 1 to the key
            lngNodeParent = lngNodeParent + 1
            'update key number if previous Parent node had children
            If lngNodeParent <= lngNodeChild Then lngNodeParent = lngNodeChild + 1
            'add the new node, with the new key and it's name
            .Nodes.Add "Source", tvwChild, [U]"N" & rs.Fields("AutoNumber").Value[/U], strNodeParent, "Folder Closed"

Do I still need to use these now I am using the AutoNumber field:

vb Code:

'add 1 to the key
 lngNodeParent = lngNodeParent + 1

Ah, I hadn't thought about that bit!

First of all I'd recommend changing the field names from AutoNumber (to NodeID etc), as it will make things easier to read.

For the Parent it should be added as you have it in the first post (but with the N change).

You don't actually need the lngNodeParent/lngNodeChild variables any more, but should instead be storing the N value (including the field value) to a variable instead. Note that if you don't have grandchild nodes, you wont need to store it for the child nodes.

You would then use that variable as the first parameter when adding the child nodes.

Quote:

---

Originally Posted by si_the_geek

First of all I'd recommend changing the field names from AutoNumber (to NodeID etc), as it will make things easier to read.

---

Done.

Quote:

---

Originally Posted by si_the_geek

For the Parent it should be added as you have it in the first post (but with the N change).

---

Done, with the "N" change.

Quote:

---

Originally Posted by si_the_geek_

You don't actually need the lngNodeParent/lngNodeChild variables any more, but should instead be storing the N value (including the field value) to a variable instead. Note that if you don't have grandchild nodes, you wont need to store it for the child nodes.

---

So what I have done here is to save into the new variable the following:
API=N22
VB Code=N23
etc

Is this what you meant?
I put the '=' in so, if needed, string manipulation would be easier, regardless of how many items added to the array.

Quote:

---

Originally Posted by si_the_geek

You would then use that variable as the first parameter when adding the child nodes.

---

Wouldn't what I almost already had do this:

vb Code:

.Nodes.Add "N" & rs.Fields("Node_ID").Value, tvwChild, "N" & rs.Fields("Node_ID").Value, strNodeChild, "Folder Closed"

Quote:

---

Originally Posted by si_the_geek

Note that if you don't have grandchild nodes, you wont need to store it for the child nodes

---

Would the actual file names (mFormatVB) be classed as GrandChildNodes?

Don't know why it started at 22. When I redid the tables I deleted the old DB and created a new one. :confused:

This is now my tbl_Nodes layout:

Node_ID..NodeName...........ChildNodeName
22..........API
23..........Databases........... Access
24..........Databases............Excel
25..........SQL
26..........VB Code.............Collections
27..........VB Code..............Forms
28..........VB Code..............Functions
29..........VB Code..............Modules
30..........VB Code..............Subs
31..........VB Code..............UDT
32..........VBA....................Access
33..........VBA....................Excel

When I get to the line of code to add:
24 - Databases - Excel
I am getting the error:
Element not found.

On this line of code:

vb Code:

.Nodes.Add "N" & rs.Fields("Node_ID").Value, tvwChild, strNodeChild, "Folder Closed"

"N" & rs.Fields("Node_ID").Value = 24
strNodeChild = Excel

I can't work this out as it loops through as (I) would expect it to, and shows the values I would expect.:confused:

I have the string array NodesKey(keyctr) that holds the Nodes key number and field value. I have added a string called OldStrNodeParent to use to loop through and load the Child Nodes.

This is what I have so far;

vb Code:

With tvwCode
    .Nodes.Add , , "Source", "Source Code", "Folder Closed"
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("NodeName").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("NodeName").Value
            OldStrNodeParent = rs.Fields("NodeName").Value
            'add the new node, with the new key and it's name
            .Nodes.Add "Source", tvwChild, "N" & rs.Fields("Node_ID").Value, strNodeParent, "Folder Closed"
            If PCtr = 0 Then
                strParent(PCtr) = strNodeParent
                NodesKey(KeyCtr) = strNodeParent & "=N" & rs.Fields("Node_ID").Value
            Debug.Print NodesKey(KeyCtr)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            Else
                ReDim Preserve strParent(UBound(strParent) + 1)
                strParent(PCtr) = strNodeParent
                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                NodesKey(KeyCtr) = strNodeParent & "=N" & rs.Fields("Node_ID").Value
            Debug.Print NodesKey(KeyCtr)
                PCtr = PCtr + 1
                KeyCtr = KeyCtr + 1
            End If
        End If
            If strNodeParent = OldStrNodeParent Then
                'if needed, add the child
                'check if there are any child nodes -
                'if Fields is "" then no new child nodes
                'move on to next parent node
                If strNodeChild <> rs.Fields("ChildNodeName").Value & "" Then
                    'if Fields is not empty - add new child node
                    strNodeChild = rs.Fields("ChildNodeName").Value & ""
                        If strNodeChild <> "" Then  '(dont add Null/blank child nodes)
                            'add the new child node with the path of the parent node
                            .Nodes.Add "N" & rs.Fields("Node_ID").Value, tvwChild, strNodeChild, "Folder Closed"
                            If CCtr = 0 Then
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                If KeyCtr > 0 Then
                                    ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & "=N" & rs.Fields("Node_ID").Value
                                Debug.Print NodesKey(KeyCtr)
                                    KeyCtr = KeyCtr + 1
                                Else
                                    NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & "=N" & rs.Fields("AutoNumber").Value
                                Debug.Print NodesKey(KeyCtr)
                                    KeyCtr = KeyCtr + 1
                                End If
                                CCtr = CCtr + 1
                            Else
                                ReDim Preserve strChild(UBound(strChild) + 1)
                                strChild(CCtr) = strNodeParent & " " & strNodeChild
                                ReDim Preserve NodesKey(UBound(NodesKey) + 1)
                                NodesKey(KeyCtr) = strNodeParent & " " & strNodeChild & "=N" & rs.Fields("AutoNumber").Value
                                CCtr = CCtr + 1
                                KeyCtr = KeyCtr + 1
                            End If
                        End If
                End If
            End If
        
        rs.MoveNext
    Loop
End With
'close the recordset
rs.Close

Quote:

---

Originally Posted by aikidokid

So what I have done here is to save into the new variable the following:
API=N22
VB Code=N23
etc

Is this what you meant?

---

Nope.. what I meant was just store the last value to a single variable (so as soon as "VB Code" loads it would contain "N23").

This is so you can use it as the first parameter to .Add, which specifies the parent node (as you are getting the first parameter from the recordset, your current code basically specifies itself - before it is added!).

Quote:

---

Would the actual file names (mFormatVB) be classed as GrandChildNodes?

---

Basically yes in most cases (in some they are just Child nodes), but technically they are Leaf nodes - they have a parent (and possibly grandparent etc), but no children of their own.

Quote:

---

Don't know why it started at 22. When I redid the tables I deleted the old DB and created a new one. :confused:

---

That sounds a bit odd, but isn't something to worry about - as long as the other table uses the same numbers it will work fine.

Quote:

---

I have the string array NodesKey(keyctr) that holds the Nodes key number and field value. I have added a string called OldStrNodeParent to use to loop through and load the Child Nodes.

---

You don't actually need these, or any of the other arrays.

By ordering the data in the recordset (I think we added an Order By?) you can be sure that all items with the same NodeName will be grouped together - and as strNodeParent etc are only set within the If statement, they will always contain the appropriate parent.

You just need strNodeParent (so that you can tell when a new parent should be added) and a string variable (perhaps strNodeParentID ?) to contain "Nxx", which allows the child nodes to be added to it.

Quote:

---

Originally Posted by si_the_geek

Basically yes in most cases (in some they are just Child nodes), but technically they are Leaf nodes - they have a parent (and possibly grandparent etc), but no children of their own.

---

So will I be adding nodes for these when I get to loading them?

Quote:

---

Originally Posted by si_the_geek

That sounds a bit odd, but isn't something to worry about - as long as the other table uses the same numbers it will work fine.

---

I have re-done the DB again, and it is starting from 1. The original DB ended at 21, so for some reason it carried on from there. :confused:

Quote:

---

Originally Posted by si_the_geek

You don't actually need these, or any of the other arrays.

---

So you wouldn't store the nodes names, Parent & Child, in the array to populate other combo boxes in the app, you would just make a call to the DB each time they were needed?

So far I have the following code, which adds all of the Parent Nodes, but is having a problem loading the Child nodes.
The error is on the line where the node is added in the second If statement.
I know what is causing the error, I just don't know how to change it to to get it to work.:confused:
It's the KEY parameter of the .Node.Add line.
I am not sure what this is or where I am getting it from.

vb Code:

With tvwCode
    .Nodes.Add , , "Source", "Source Code", "Folder Closed"
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("NodeName").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("NodeName").Value
            'add the new node, with the new key and it's name
            .Nodes.Add "Source", tvwChild, "N" & rs.Fields("Node_ID").Value, strNodeParent, "Folder Closed"
            strNodeParentID(KeyCtr) = "N" & rs.Fields("Node_ID").Value
            Debug.Print strNodeParentID(KeyCtr)
        End If
 
        If strNodeChild <> rs.Fields("ChildNodeName").Value Then
            Do While Not rs.EOF
                strNodeChild = rs.Fields("ChildNodeName").Value
                .Nodes.Add strNodeParentID(KeyCtr), tvwChild, strNodeChild, "Folder Closed"
                strNodeParentID(KeyCtr) = "N" & rs.Fields("Node_ID").Value
                Debug.Print strNodeParentID(KeyCtr)
            Loop
        End If
           
        KeyCtr = KeyCtr + 1
        ReDim Preserve strNodeParentID(UBound(strNodeParentID) + 1)
                
        rs.MoveNext
    Loop
End With

Quote:

---

Originally Posted by aikidokid

So will I be adding nodes for these when I get to loading them?

---

Yep.

Quote:

---

So you wouldn't store the nodes names, Parent & Child, in the array to populate other combo boxes in the app, you would just make a call to the DB each time they were needed?

---

Correct.. as the chances are you don't want exactly the same data - and the database is far better at finding/sorting data than VB code can ever be (actually for an Access database on the local computer that isn't completely true, but it will be less work!).

Quote:

---

So far I have the following code, which adds all of the Parent Nodes, but is having a problem loading the Child nodes.
The error is on the line where the node is added in the second If statement.
I know what is causing the error, I just don't know how to change it to to get it to work.:confused:
It's the KEY parameter of the .Node.Add line.
I am not sure what this is or where I am getting it from.

---

You don't need it - strNodeParentID should just be a string (containing the latest parent), not an array of strings.

This also removes the need for KeyCtr, and ReDim Preserve (which is so slow it would lose a race to a dead snail!).

By the way, that inner Do Loop shouldn't be there. ;)

Quote:

---

Originally Posted by si_the_geek

You don't need it

---

That explains why I couldn't find it :lol:

Quote:

---

Originally Posted by si_the_geek

By the way, that inner Do Loop shouldn't be there.

---

I had already removed that. It was just one of my attempts to get it to work before I posted - again. ;)

Just in case somebody does a search, this is what I have and it works as expected.
BTW. It's a lot less code than I had before. :D

vb Code:

With tvwCode
    .Nodes.Add , , "Source", "Source Code", "Folder Closed"
    Do While Not rs.EOF
        'check if node is same or different
        If strNodeParent <> rs.Fields("NodeName").Value Then
            'if different, it's a new parent node
            strNodeParent = rs.Fields("NodeName").Value
            'add the new node, with the new key and it's name
            .Nodes.Add "Source", tvwChild, "N" & rs.Fields("Node_ID").Value, strNodeParent, "Folder Closed"
            strNodeParentID = "N" & rs.Fields("Node_ID").Value
        End If
 
        If strNodeChild <> rs.Fields("ChildNodeName").Value Then
            strNodeChild = rs.Fields("ChildNodeName").Value
            .Nodes.Add strNodeParentID, tvwChild, , strNodeChild, "Folder Closed"
        End If
       
        rs.MoveNext
    Loop
End With