J
JohnE
Hello. I am using the datAdrenaline (Brent Spaulding) treeview sample as the
background for a form in the app that I am currently working on. So if
anyone is familiar with the sample, great. Let me set the stage for what I
am doing;
- there is one table (tblChangeRequest) that is being used
- this table will have all of the change requests (assigned and unassigned)
that are logged in
- there are 2 fields in the table that are being used to separate out the
change requests
- the 2 fields are IsTopLevel (number) and IsChildOf (number)
- some change requests that are submitted will be top level requests and
other change requests could be children of the top level or of a sub level.
An example is Job Costing could be top level. Under it there could be job
costing ph1 and job costing ph2. Under those 2 there could be even more
levels. You can see where this is going in that the number of levels can
vary from CR to CR. Which is what is making the treeview sample so good as
it allows multiple levels.
- the treeview is to show only the assigned and open CR
- the listview will show only the unassigned CR
- the code to fill the treeview on form load is as follows;
'=============================
Dim strSQL As String
Dim tv As TreeView
'building the treeview
strSQL = "SELECT BuildKey(ChangeRequestID) As NodeKey, ChangeRequest As
NodeText, BuildKey(Nz(IsChildOf,0)) As BelongTo" & _
" FROM tblChangeRequest" & _
" WHERE IsTopLevel = 1" & _
" UNION ALL SELECT TOP 1 BuildKey('<NULL>') As NodeKey, '<
Un-Assigned >' As NodeText, BuildKey(0) As BelongsTo" & _
" FROM tblChangeRequest" & _
" ORDER BY NodeText"
'populating the tree
Set tv = Me.tvwAssigned.Object
tv.Nodes.Clear
AddNodes tv, strSQL
'go to the first node and update the list
tvwAssigned_NodeClick GotoFirstNode(tv)
On Error GoTo 0
Exit Sub
Form_Load_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Load of VBA Document Form_frmChangeRequestHierarchy"
'=============================
- the WHERE clause is added so as to only show the top level CR. There will
also need to be an AND in there for the status, which I have not worked on
yet.
- the AddNodes code is in a module and is as follows. From tracing it thru,
I think the issue is in the first Do Until... Loop, but not sure.
'=============================
Public Sub AddNodes(tv As TreeView, varSource As Variant, _
Optional nodParentNode As Node = Nothing, _
Optional intKeyFieldOrdinal As Integer = 0, _
Optional intTextFieldOrdinal As Integer = 1, _
Optional intParentKeyFieldOrdinal As Integer = 2, _
Optional intTagFieldOrdinal As Integer = -1, _
Optional strRootBelongsTo As String =
tv_conRootBelongsTo, _
Optional blPositioningRequired As Boolean = True)
'This sub Loads a TreeView control from a passed hierarchial source. The
'source can be a SQL Statement, DAO.Recordset, or ADODB.Recordset
'The field ordinals are optional and the defaults assume the recordset was
'built with the fields in the order listed in the parameter block. The TAG
'field ordinal is defaulted to -1 meaning the field does not exist.
'
'Parameters:
'tv: the TreeView object. To get the TreeView passed as a TreeView type, in
the calling
' proc use something like AddNodes(Me.tvMyTreeView.Object, ...)
'varSource: The record source with the hierarchial records you wish to put
in the tree
'nodParentNode: The parent node of the node that is getting ready to be
created
'intKeyFieldOrdinal: Field ordinal that holds the Nodes KEY
'intTextFieldOrdinal: Field ordinal that holds the Nodes TEXT value
'intParentKeyFieldOrdinal: Field ordinal that holds the Nodes PARENT key value
'intTagFieldOrdinal: Field ordinal that holds the Nodes TAG value, this field
' is not required in the records
'strRootBelongsTo: The value passed in this parameter is the value of the
"BelongsTo"
' field for the records that are on the "Root" of the tree.
'blPositioningRequired: If your recorset is ordered properly, ie: by
HierarchialLevel and/or FullProcessPath
' then you can set this parameter to False so only ONE
pass through
' the recordset is required to build the tree. If you
do not have the
' luxury of knowing what hierarchial level each record
is, then set this
' parameter to TRUE (which is the default)
'
'Assumptions:
'IF nodParentNode is NOTHING, then the value of tv_RootBelongsTo is
'used to 'Find' the root level nodes in the passed varSource.
Dim nodNode As Node 'A node created in the tree
Dim strKey As String 'The key of the node
Dim strText As String 'The value that is shown on the tree
Dim strParentKey As String 'The key to search for in the recordset
to find all the
'children of the passed node. If
nodParentNode is Nothing
'then the tv_RootBelongsTo value is used.
Dim varNodes As Variant 'The nodes that build the tree
Dim X As Long 'A counter
'Test for the type and validity of the source
Select Case True
Case TypeOf varSource Is ADODB.Recordset, TypeOf varSource Is
DAO.Recordset
Set varNodes = varSource.Clone
Case VarType(varSource) = vbString
Set varNodes = New ADODB.Recordset
varNodes.Open varSource, CurrentProject.Connection,
adOpenStatic, adLockReadOnly, adCmdText
End Select
'Ensure we have records
If varNodes.EOF And varNodes.BOF Then
Exit Sub
End If
'Determine whether to build the whole tree, or just one node
If nodParentNode Is Nothing Then
'Add ALL the records to the tree. All the records MAY initially be
added
'to the ROOT, this may seem weird, but they are positioned later.
With varNodes
.MoveFirst
Echo False, "Adding the nodes ..."
Do Until .EOF
'Get the key for the new node
strKey = Trim(.Fields(intKeyFieldOrdinal))
'Get the text for the new node
strText = Trim(.Fields(intTextFieldOrdinal))
'Get the parent key
strParentKey = Trim(.Fields(intParentKeyFieldOrdinal))
'Add the node. If positioning required, then add all the
nodes to the
'root first, then position them later. If positioning NOT
required, then
'add the node in the appropriate spot.
If blPositioningRequired = True Then
Set nodNode = tv.Nodes.Add(, , strKey, strText)
Else
If strParentKey = strRootBelongsTo Then
Set nodNode = tv.Nodes.Add(, , strKey, strText)
Else
Set nodNode = tv.Nodes.Add(tv.Nodes(strParentKey),
tvwChild, strKey, strText)
End If
End If
'Add the tag to the new node
If intTagFieldOrdinal >= 0 Then
nodNode.Tag = .Fields(intTagFieldOrdinal)
End If
'Move to the next node in the recordset to be added
.MoveNext
Loop
End With
'Now that all the nodes are on the tree, then POSITION them correctly
'based on the fact that the recordset has a 'BelongsTo' field of some
'sort. This is only done if positioning is required. Note, ast this
'point we are at the EOF, so for efficientcy, we work from the EOF to
'the BOF.
If blPositioningRequired = True Then
'Loop through the records, positioning each one, ignoring the
nodes that are supposed to be on the root
Echo False, "Positioning the nodes ..."
With varNodes
.MoveFirst
Do Until .EOF
If .Fields(intParentKeyFieldOrdinal) <> strRootBelongsTo
Then
'Get the key of the node that matches the current
record
strKey = Trim(.Fields(intKeyFieldOrdinal))
'Get the key for the PARENT of the current record
strParentKey = Trim(.Fields(intParentKeyFieldOrdinal))
'Position the node
Set tv.Nodes(strKey).Parent = tv.Nodes(strParentKey)
End If
'Move to the next record/node in the recordset
.MoveNext
Loop
End With
End If
'Now that all the nodes are there, and positions are correct,
'sort and collapse all the nodes.
Echo False, "Sorting the nodes ..."
SortNodes tv
Echo False, "Collapsing the nodes ..."
CollapseNodes tv
Else
RebuildNode tv, varNodes, nodParentNode, intKeyFieldOrdinal,
intTextFieldOrdinal, intParentKeyFieldOrdinal, intTagFieldOrdinal,
strRootBelongsTo
End If
'Clean up
Echo True
varNodes.Close
Set varNodes = Nothing
End Sub
'=============================
I am at a loss here on this and ask for assistance.
Thanks ... John
background for a form in the app that I am currently working on. So if
anyone is familiar with the sample, great. Let me set the stage for what I
am doing;
- there is one table (tblChangeRequest) that is being used
- this table will have all of the change requests (assigned and unassigned)
that are logged in
- there are 2 fields in the table that are being used to separate out the
change requests
- the 2 fields are IsTopLevel (number) and IsChildOf (number)
- some change requests that are submitted will be top level requests and
other change requests could be children of the top level or of a sub level.
An example is Job Costing could be top level. Under it there could be job
costing ph1 and job costing ph2. Under those 2 there could be even more
levels. You can see where this is going in that the number of levels can
vary from CR to CR. Which is what is making the treeview sample so good as
it allows multiple levels.
- the treeview is to show only the assigned and open CR
- the listview will show only the unassigned CR
- the code to fill the treeview on form load is as follows;
'=============================
Dim strSQL As String
Dim tv As TreeView
'building the treeview
strSQL = "SELECT BuildKey(ChangeRequestID) As NodeKey, ChangeRequest As
NodeText, BuildKey(Nz(IsChildOf,0)) As BelongTo" & _
" FROM tblChangeRequest" & _
" WHERE IsTopLevel = 1" & _
" UNION ALL SELECT TOP 1 BuildKey('<NULL>') As NodeKey, '<
Un-Assigned >' As NodeText, BuildKey(0) As BelongsTo" & _
" FROM tblChangeRequest" & _
" ORDER BY NodeText"
'populating the tree
Set tv = Me.tvwAssigned.Object
tv.Nodes.Clear
AddNodes tv, strSQL
'go to the first node and update the list
tvwAssigned_NodeClick GotoFirstNode(tv)
On Error GoTo 0
Exit Sub
Form_Load_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Load of VBA Document Form_frmChangeRequestHierarchy"
'=============================
- the WHERE clause is added so as to only show the top level CR. There will
also need to be an AND in there for the status, which I have not worked on
yet.
- the AddNodes code is in a module and is as follows. From tracing it thru,
I think the issue is in the first Do Until... Loop, but not sure.
'=============================
Public Sub AddNodes(tv As TreeView, varSource As Variant, _
Optional nodParentNode As Node = Nothing, _
Optional intKeyFieldOrdinal As Integer = 0, _
Optional intTextFieldOrdinal As Integer = 1, _
Optional intParentKeyFieldOrdinal As Integer = 2, _
Optional intTagFieldOrdinal As Integer = -1, _
Optional strRootBelongsTo As String =
tv_conRootBelongsTo, _
Optional blPositioningRequired As Boolean = True)
'This sub Loads a TreeView control from a passed hierarchial source. The
'source can be a SQL Statement, DAO.Recordset, or ADODB.Recordset
'The field ordinals are optional and the defaults assume the recordset was
'built with the fields in the order listed in the parameter block. The TAG
'field ordinal is defaulted to -1 meaning the field does not exist.
'
'Parameters:
'tv: the TreeView object. To get the TreeView passed as a TreeView type, in
the calling
' proc use something like AddNodes(Me.tvMyTreeView.Object, ...)
'varSource: The record source with the hierarchial records you wish to put
in the tree
'nodParentNode: The parent node of the node that is getting ready to be
created
'intKeyFieldOrdinal: Field ordinal that holds the Nodes KEY
'intTextFieldOrdinal: Field ordinal that holds the Nodes TEXT value
'intParentKeyFieldOrdinal: Field ordinal that holds the Nodes PARENT key value
'intTagFieldOrdinal: Field ordinal that holds the Nodes TAG value, this field
' is not required in the records
'strRootBelongsTo: The value passed in this parameter is the value of the
"BelongsTo"
' field for the records that are on the "Root" of the tree.
'blPositioningRequired: If your recorset is ordered properly, ie: by
HierarchialLevel and/or FullProcessPath
' then you can set this parameter to False so only ONE
pass through
' the recordset is required to build the tree. If you
do not have the
' luxury of knowing what hierarchial level each record
is, then set this
' parameter to TRUE (which is the default)
'
'Assumptions:
'IF nodParentNode is NOTHING, then the value of tv_RootBelongsTo is
'used to 'Find' the root level nodes in the passed varSource.
Dim nodNode As Node 'A node created in the tree
Dim strKey As String 'The key of the node
Dim strText As String 'The value that is shown on the tree
Dim strParentKey As String 'The key to search for in the recordset
to find all the
'children of the passed node. If
nodParentNode is Nothing
'then the tv_RootBelongsTo value is used.
Dim varNodes As Variant 'The nodes that build the tree
Dim X As Long 'A counter
'Test for the type and validity of the source
Select Case True
Case TypeOf varSource Is ADODB.Recordset, TypeOf varSource Is
DAO.Recordset
Set varNodes = varSource.Clone
Case VarType(varSource) = vbString
Set varNodes = New ADODB.Recordset
varNodes.Open varSource, CurrentProject.Connection,
adOpenStatic, adLockReadOnly, adCmdText
End Select
'Ensure we have records
If varNodes.EOF And varNodes.BOF Then
Exit Sub
End If
'Determine whether to build the whole tree, or just one node
If nodParentNode Is Nothing Then
'Add ALL the records to the tree. All the records MAY initially be
added
'to the ROOT, this may seem weird, but they are positioned later.
With varNodes
.MoveFirst
Echo False, "Adding the nodes ..."
Do Until .EOF
'Get the key for the new node
strKey = Trim(.Fields(intKeyFieldOrdinal))
'Get the text for the new node
strText = Trim(.Fields(intTextFieldOrdinal))
'Get the parent key
strParentKey = Trim(.Fields(intParentKeyFieldOrdinal))
'Add the node. If positioning required, then add all the
nodes to the
'root first, then position them later. If positioning NOT
required, then
'add the node in the appropriate spot.
If blPositioningRequired = True Then
Set nodNode = tv.Nodes.Add(, , strKey, strText)
Else
If strParentKey = strRootBelongsTo Then
Set nodNode = tv.Nodes.Add(, , strKey, strText)
Else
Set nodNode = tv.Nodes.Add(tv.Nodes(strParentKey),
tvwChild, strKey, strText)
End If
End If
'Add the tag to the new node
If intTagFieldOrdinal >= 0 Then
nodNode.Tag = .Fields(intTagFieldOrdinal)
End If
'Move to the next node in the recordset to be added
.MoveNext
Loop
End With
'Now that all the nodes are on the tree, then POSITION them correctly
'based on the fact that the recordset has a 'BelongsTo' field of some
'sort. This is only done if positioning is required. Note, ast this
'point we are at the EOF, so for efficientcy, we work from the EOF to
'the BOF.
If blPositioningRequired = True Then
'Loop through the records, positioning each one, ignoring the
nodes that are supposed to be on the root
Echo False, "Positioning the nodes ..."
With varNodes
.MoveFirst
Do Until .EOF
If .Fields(intParentKeyFieldOrdinal) <> strRootBelongsTo
Then
'Get the key of the node that matches the current
record
strKey = Trim(.Fields(intKeyFieldOrdinal))
'Get the key for the PARENT of the current record
strParentKey = Trim(.Fields(intParentKeyFieldOrdinal))
'Position the node
Set tv.Nodes(strKey).Parent = tv.Nodes(strParentKey)
End If
'Move to the next record/node in the recordset
.MoveNext
Loop
End With
End If
'Now that all the nodes are there, and positions are correct,
'sort and collapse all the nodes.
Echo False, "Sorting the nodes ..."
SortNodes tv
Echo False, "Collapsing the nodes ..."
CollapseNodes tv
Else
RebuildNode tv, varNodes, nodParentNode, intKeyFieldOrdinal,
intTextFieldOrdinal, intParentKeyFieldOrdinal, intTagFieldOrdinal,
strRootBelongsTo
End If
'Clean up
Echo True
varNodes.Close
Set varNodes = Nothing
End Sub
'=============================
I am at a loss here on this and ask for assistance.
Thanks ... John