Populating TreeView

[shaunhubbs]

Hi all,

I am having a problem with populating my TreeView once I get down to the 4th level (way at the bottom in red). In my code below you can ignore the SQL SELECT statements that I am using to set the sources as they are correct. I have tested them in separate views.

But once I get to the 4th level fill I get a RT error 35601 "Element not found". In my 4th level view there are, in fact, records retrieved, but this piece of code crashes at that point.

Does anyone see what is wrong with the code? The 4th level is a copy of the 3rd level with the relevant number changed from 3 to 4. Does anyone know how I could put in a check at this point that would keep the code from exiting and erroring if, in fact, it is not finding an element for some reason...?

Thanks.

- Shaun

Private Sub Form_Load()

   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT dbo.Z_SH_explosions.* FROM dbo.Z_SH_explosions WHERE (lvl = 1)"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
    
    ' Fill Level 1 with Sets
    Do Until rs.EOF
        Me!axTreeView.Nodes.Add , , "s" & rs!urn, rs!item
        rs.MoveNext
    Loop
    rs.Close

    ' Fill Level 2 with items in sets
    Dim strOrderKey As String
    
    With rs
        .Source = "SELECT parent.urn AS parent_urn, parent.item AS parent_descr, child.urn AS urn, child.item AS item, child.descr, child.mfg_nbr, child.lft, child.rgt, child.lvl, (SELECT     MAX(parent.item) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_name, (SELECT MAX(parent.descr) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_descr FROM dbo.Z_SH_explosions AS parent INNER JOIN dbo.Z_SH_explosions AS child ON parent.item<>child.item WHERE (child.lft BETWEEN parent.lft AND parent.rgt) AND (NOT EXISTS (SELECT * FROM dbo.Z_SH_explosions AS middle WHERE middle.lft BETWEEN parent.lft AND parent.rgt AND child.lft BETWEEN middle.lft AND middle.rgt AND middle.item NOT IN (child.item, parent.item))) AND (parent.item <> 'SETS') AND child.lvl = 2"
        .Open
    End With

    Do Until rs.EOF
        ' Link to Level 1 by referencing the urn (Unique Record Number) and set
        ' the node as a child node of Level 1. Use "l2" and the
        ' StrConv() function in the new Key property for Level 2,
        ' because urn is a numeric field.
        strOrderKey = StrConv("l2" & rs!urn, vbLowerCase)
        Me!axTreeView.Nodes.Add "s" & rs!parent_urn, tvwChild, strOrderKey, _
           rs!item
        rs.MoveNext
    Loop
    rs.Close

    ' Fill Level 3.
    With rs
        .Source = "SELECT parent.urn AS parent_urn, parent.item AS parent_descr, child.urn AS urn, child.item AS item, child.descr, child.mfg_nbr, child.lft, child.rgt, child.lvl, (SELECT     MAX(parent.item) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_name, (SELECT MAX(parent.descr) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_descr FROM dbo.Z_SH_explosions AS parent INNER JOIN dbo.Z_SH_explosions AS child ON parent.item<>child.item WHERE (child.lft BETWEEN parent.lft AND parent.rgt) AND (NOT EXISTS (SELECT * FROM dbo.Z_SH_explosions AS middle WHERE middle.lft BETWEEN parent.lft AND parent.rgt AND child.lft BETWEEN middle.lft AND middle.rgt AND middle.item NOT IN (child.item, parent.item))) AND (parent.item <> 'SETS') AND child.lvl = 3"
        .Open
    End With

    Do Until rs.EOF
        ' Link to Level 2 by referencing the urn and set
        ' the node as a child node of Level 2. Use "l3" and the
        ' StrConv() function in the new Key property for Level 3,
        ' because urn is a numeric field.
        strOrderKey = StrConv("l3" & rs!urn, vbLowerCase)
        Me!axTreeView.Nodes.Add "l2" & rs!parent_urn, tvwChild, strOrderKey, _
           rs!item
        rs.MoveNext
    Loop
    rs.Close

[COLOR=red]    
    ' Fill Level 4.
    With rs
        .Source = "SELECT parent.urn AS parent_urn, parent.item AS parent_descr, child.urn AS urn, child.item AS item, child.descr, child.mfg_nbr, child.lft, child.rgt, child.lvl, (SELECT     MAX(parent.item) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_name, (SELECT MAX(parent.descr) FROM dbo.Z_SH_explosions parent INNER JOIN dbo.Z_SH_explosions child ON parent.item <> child.item WHERE child.lft BETWEEN parent.lft AND parent.rgt AND parent.lvl = 1) AS set_descr FROM dbo.Z_SH_explosions AS parent INNER JOIN dbo.Z_SH_explosions AS child ON parent.item<>child.item WHERE (child.lft BETWEEN parent.lft AND parent.rgt) AND (NOT EXISTS (SELECT * FROM dbo.Z_SH_explosions AS middle WHERE middle.lft BETWEEN parent.lft AND parent.rgt AND child.lft BETWEEN middle.lft AND middle.rgt AND middle.item NOT IN (child.item, parent.item))) AND (parent.item <> 'SETS') AND child.lvl = 4"
        .Open
    End With

    Do Until rs.EOF
        ' Link to Level 3 by referencing the urn and set
        ' the node as a child node of Level 3. Use "l4" and the
        ' StrConv() function in the new Key property for Level 4,
        ' because urn is a numeric field.
        strOrderKey = StrConv("l4" & rs!urn, vbLowerCase)
        Me!axTreeView.Nodes.Add "l3" & rs!parent_urn, tvwChild, strOrderKey, _
           rs!item
        rs.MoveNext
    Loop
    rs.Close
[/color]

    Set rs = Nothing

End Sub

 

[TGandon]

Shaun

You can put in a simple check using

Public Sub NodeExists(ByRef Tree As TreeView, ByVal Key As String)As Boolean

On Error Resume Next
' If Tree.Nodes(Key) doesn't exist, error occurs and function returns false
NodeExists = (Tree.Nodes(Key).Text <> "")

End Function

Then in your code

strOrderKey = StrConv("l4" & rs!urn, vbLowerCase)
If NodeExists(axTreeView, "l3" & rs!parent_urn) Then
   Me!axTreeView.Nodes.Add "l3" & rs!parent_urn, tvwChild, strOrderKey, rs!item
End If

Trevor

 

[shaunhubbs]

Thanks for the response Trevor. I am hoping you can help me troubleshoot a couple of things with the code though.

1) Should the first piece of code be declared as a "Public Function" rather than a "Public Sub"?

2) Any idea why I would be getting a 'Type Mismatch' when the code goes to execute the "If NodeExists" line in the second piece of code?

Thanks again.

- Shaun

 

[gmmastros]

The first code block by TGandon should be Public Function because it is returning a value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shaunhubbs]

Thanks George. That seems to let it all compile now, but I am still getting the 'Type Mismatch'.

Anyone have any ideas on my question #2? The parameters I am passing to the function appear to be a treeview and a string so why would I get a type mismatch?

Thanks.

- Shaun

 

[gmmastros]

In the VB IDE, scroll to the VERY top of the code window. If you don't see (as the first line of code)

Option Explicit

Then put it in. By using option explicit, you are forced to declare EVERY variable and object that you use in your code. After adding Option Explicit, you should do a re-compile. Any variable that you are using that was not explicitly declared will be highlighted. This may not resolve your problem, but it may help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shaunhubbs]

I tried using the Option Explicit at the top of my code (what did you mean by IDE George?) and I still get the Type Mismatch. Also, to do the 'Compile' from the 'Run' menu I had to edit some declarations that I got away without having before.

Any other ideas as to why I would still get a type mismatch? Thanks George and Trevor for your help so far.

- Shaun

 

[shaunhubbs]

BAH! It was an error in my table. Something got a little messed around in my data and wouldn't load into the tree correctly.

Thanks to both of you again for your help though.

- Shaun