shaunhubbs
Technical User
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
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
Code:
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