Treeview - populating nodes on demand

Hi,

I'm  using a treeview control in Access 2007 to display and allow the user to select parts or their sub parts in a database used in manufacturing. I have no problems getting treeview to work but I would like it to populate the nodes only when a higher level node is expanded rather than populating the whole lot when the form is first loaded as there are potentially a lot of nodes. Can anyone suggest how I might do this?
Steve-PEMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
There is a great mdb demo at the bottom of this link:

http://www.datapigtechnologies.com/AccessMain.htm

 DataPig Instant TreeView See Tutorial

even includes a video tutorial.  A great way to jump start a treeview project

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve-PEMAuthor Commented:
Thanks mx - I'll have a look through the tutorial and see it helps

Steve
0
Steve-PEMAuthor Commented:
I've had a look at the datapig link. It's a nice solution to straight foward treeview use. However the Treeview control is I think limited to 32000 nodes, not that you would ever want that many up at once. My problem is that whilst my treeview only needs to display a few hundred nodes at most it has to get these from a recordset several hundred thousand records long. Hence I need to dynamically add and delete nodes.

Below is the code I finally came up with which seems to work well with my limited test dataset. I have not tested it with a full dataset or in a multi user envionment, but it may help soneone with a similar problem.

If anyone has a comment to make on the code please bear in mind I'm not an expert (or a programmer...) - so take it easy please!

Steve

PS What do I do about the points?


Option Compare Database
'lngUniqueNodeNumber provides an incrementing number that
'ensures that any node's key is unique
'It is required as a part may appear in more than one place in the TreeView
'and node keys are required to be unique
Private lngUniqueNodeKey As Long

Private Sub Form_Open(Cancel As Integer)

Dim cnn  As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst_PartsList As New ADODB.Recordset
rst_PartsList.ActiveConnection = cnn
rst_PartsList.CursorLocation = adUseClient
Dim strSQL As String
Dim strPEMPart As String
Dim strNodeKey As String

lngUniqueNodeKey = 1 'initialise module level variable at one
strPEMPart = "23813" ' PEM Part number of the universal set part.
'set up tree view parameters
strNodeKey = Format(lngUniqueNodeKey, "00000000") & "-" & strPEMPart

  With Me.PartsTView
    .Style = tvwTreelinesPlusMinusText
    .LineStyle = tvwRootLines
    .Indentation = 240
    .Appearance = ccFlat
    .HideSelection = False
    .BorderStyle = ccFixedSingle
    .HotTracking = True
    .FullRowSelect = True
    .Checkboxes = False
    .SingleSel = False
    .Sorted = False
    .Scroll = True
    .LabelEdit = tvwManual
    .Font.Name = "Ariel"
    .Font.Size = 9
  End With
  
strSQL = "SELECT * FROM PEMPart WHERE [PEMPart]= " & strPEMPart
rst_PartsList.Open strSQL, cnn, adOpenDynamic, adLockReadOnly
'Add node of the Universal Set part to top level of TreeView
Me.PartsTView.Nodes.Add Text:=rst_PartsList!Part, Key:=strNodeKey
'Populate first level of sub parts
PopulateSubNodesTView strNodeKey, True
'Update the txtPEMPart TextBox
Me.txtPEMPart = CLng(strPEMPart)

'tidy up
rst_PartsList.Close
cnn.Close
Set rst_PartsList = Nothing
Set cnn = Nothing

End Sub

Private Sub PopulateSubNodesTView(strNodeKey As String, OneNodeOnly As Boolean)
    
Dim cnn  As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst_PartsList As New ADODB.Recordset
rst_PartsList.ActiveConnection = cnn
rst_PartsList.CursorLocation = adUseClient

Dim rst_PartData As New ADODB.Recordset
rst_PartData.ActiveConnection = cnn
rst_PartData.CursorLocation = adUseClient

Dim strSQL As String
Dim strPEMPart As String
Dim strSubNodeKey As String
'Populate nodes down one level from ParentPart node
'also populate at least one node on a further level down to activate the plus-minus box
strPEMPart = (Right(strNodeKey, Len(strNodeKey) - 9))
strSQL = "SELECT * FROM SubParts WHERE [ParentPart]= " & strPEMPart
rst_PartsList.Open strSQL, cnn, adOpenDynamic, adLockReadOnly

With rst_PartsList
    If Not .EOF Then 'if recordset empty terminate
        .MoveFirst
        Do Until .EOF
            'populate the nodes
            strPEMPart = CStr(rst_PartsList!SubPart)
            strSQL = "SELECT * FROM PEMPart WHERE [PEMPart]= " & strPEMPart
            rst_PartData.Open strSQL, cnn, adOpenDynamic, adLockReadOnly
            lngUniqueNodeKey = lngUniqueNodeKey + 1
            strSubNodeKey = Format(lngUniqueNodeKey, "00000000") & "-" & strPEMPart
            Me.PartsTView.Nodes.Add Relationship:=tvwChild, Relative:=strNodeKey, Text:=rst_PartData!Part, Key:=strSubNodeKey
            'populate just one node to the sub node to enable plus minus box display
            PopulateSubNodesTView strSubNodeKey, True
            rst_PartData.Close
            Set rst_PartData = Nothing
            .MoveNext
        Loop
    End If
End With

'tidy up
rst_PartsList.Close
cnn.Close
Set rst_PartsList = Nothing
Set cnn = Nothing

End Sub

Private Sub PartsTView_NodeClick(ByVal Node As Object)

'on mode click load all subnodes into treeview control
'the expand clicked  node
Dim NodeKey As String
Dim varNode As Node
Dim lngPEMPart As Long
Set varNode = Node

'clear all existing sub nodes
Do While Node.Children
    Me.PartsTView.Nodes.Remove Node.Child.Index
Loop

PopulateSubNodesTView varNode.Key, False

'Update txtPEMPart TextBox as long integer
lngPEMPart = CLng((Right(varNode.Key, Len(varNode.Key) - 9)))

Me.txtPEMPart = lngPEMPart
End Sub

Open in new window

0
Patrick MatthewsCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.