Solved

Create TreeView Nodes From Recordset

Posted on 2011-03-25
4
992 Views
Last Modified: 2012-05-11
Hi All

I am attempting to populate a VB6 TreeView from a Recordset that holds Appointment Data. The TreeView Structure is Staff/Resource (Parent), AppointmentType (Child), Appointments (GrandChild).

As I iterate through all appointments in recordset, which e.g has only 1 Parent, 3 Children and 20 GrandChildren; I am unsure how to only create the RootNode once and ChildNode 3 times. The Grandchildren are easy as they are required through each Loop.

I would like to use a DISTINCT query on the Recordset to get my Node Levels but have found this impossible.

FLOG51
Dim queryAll as String
Dim appRS as object
Dim NodeX as Node
queryAll = "SELECT * FROM Appointment WHERE appDate = CURDATE() ORDER BY Staff/Resource, appontmentType, startTime"
   
Set appRS = gDBUtility.getRSA(queryAll)'This populates RS.
   If Not appRS Is Nothing Then
        While Not appRS.EOF
        'I understand all the Add Nodes syntax
        'Child
        Set NodeX = TreeView1.Nodes.Add(,,aappRS!Staff,appRS!Staff)
     ' However above will create the same node every time and Crash. 
        'Child
        Set NodeX = tv1.Nodes.Add(appRS!Staff,tvwChild,appRS!uniqueKey,appRS!appointmentType) 
          appRS.MoveNext
        Wend
   appRS.Close
   Set appRS = Nothing
   End If

Open in new window

0
Comment
Question by:FLOG51
  • 3
4 Comments
 
LVL 4

Expert Comment

by:coolcurrent4u
Comment Utility
first of all you did not tell the error you where getting, you said impossible
second since you need distinct result, you query should be

 "SELECT DISTINCT Appointment.* FROM Appointment WHERE appDate = CURDATE() ORDER BY Staff/Resource, appontmentType, startTime"
0
 

Author Comment

by:FLOG51
Comment Utility
Hi coolcurrent4u:

This is not appropriate as I also need all the individual booking data ie. the DISTINCT call will deliver 1 value for each appointment type, whereas there could be hundreds of values for each appointment type. I do not want to run multipe queries to get the node info.

I only need to run one query and create Parent, Child and GrandChild nodes from the one Recordset.

I have worked the code out and will post here later to help anyone else who has similar issue.

FLOG51
0
 

Accepted Solution

by:
FLOG51 earned 0 total points
Comment Utility
Ok here is the code (attached) to handle this with one Recordset.

Each time through the While Statement:
1: attempt to create a Parent node with the RS value of "Resource"; If not found create Parent Node and use "Resource" value as Key. In this way Distinct Parent Nodes are created.
2: attempt to create a Child Node if not found create using Unique combo of "resource & "_" & appType" as the Key. In this way Distinct Child Nodes are created.
3:Create the GrandChildren Nodes using Unique Key (I Concatonated a String using Resouce, Staff, StartTime, EndTime, Date and AppType). In this way Distinct GrandChild Nodes are created.

I have purposefully created a Key for Grandchildren even though I will not be creating any GreatGrandChildren Nodes. This, as I reference this Key when Deleting Nodes or other events for reports etc.

Hope this helps someone.
FLOG51.

Private Sub FillTVNodes()
'Clear any TreeView Data
    TreeView.nodes.clear
    
    Dim appSQL As String
    Dim resourceSQL As String
    Dim Site As String
    
    Site = cmbSite.list(cmbSite.listIndex)
    ' Query to get appointment Data for selected Date
    appSQL = "select * from Appointment where AppDate = " & flxDate(for_Date) & " and site = '" & EscapeSpecial(Site) & "'"
    ' Get the Staff and Resource Values for query.
    appSQL = appSQL & " and " & flxFmtName("staff") & " in (" & resourceSQL & ") "
    ' Order output by StartTime then Resource Name   
    appSQL = appSQL & " order by starttime ASC, Resource ASC "
    
    
    ' Fill RS With list of staff members who have bookings today and add their bookings
    Set appRS = gDBUtility.getRSA(appSQL)
    If Not appRS Is Nothing Then
        While Not appRS.EOF
            'Call Sub to Create Parent
            AddTreeViewParentNode appRS!staff
            'Call Sub to Create Child
            AddTreeViewChildNode appRS!appType, appRS!resource, appRS!startTime, appRS!endTime
            'Call Sub to Create GrandChildren
            AddTreeViewGrandChildNode appRS!AppKey, for_Date, appRS!staff, appRS!cardNumber, appRS!startTime, appRS!endTime, appRS!appType, appRS!MemberNumber, appRS!MemberType, appRS!firstname, appRS!lastname, False, False, appRS
            appRS.MoveNext
        Wend
        appRS.Close
        Set appRS = Nothing
    End If
End Sub


Private Sub AddTreeViewParentNode(ByVal resource As String)
    Dim nodX As Node
    ' See if Parent Node already exist if not continue.
    If FindNode(resource, nodX) = False Then
    	'Create the Parent Node for Resource.
        Set nodX = TreeView.nodes.Add(, , resource, resource)
        
        On Error Resume Next
        Dim resourceOrStaffB As Boolean
        ' may not be a current resource anymore
        resourceOrStaffB = resourceOrStaff.Item(resource)
        err.clear
        ' Set the image for Node based on Resource or Staff outcome.
        SetStaffImage nodX, resourceOrStaffB
    End If
        Set nodX = Nothing
End Sub

Private Sub AddTreeViewChildNode(ByVal appType As String, ByVal resource As String, ByVal startTime As Date, ByVal endTime As Date)
    On Error Resume Next
    Dim resourceOrStaffB As Boolean
    ' Create Child Key and Text here
    Dim bookingChildNodeKey As String
    Dim bookingChildNodeText As String
    
    ' Create the Text for this Child Node
    bookingChildNodeText = appUtil.appTimeFormatStr(startTime) & " - " & appUtil.appTimeFormatStr(endTime) & " | " & appType
    ' Create Unique Key for Child Node.
    bookingChildNodeKey = resource & "_" & appType

    ' may not be a current resource anymore False = Staff / True = Resource
    resourceOrStaffB = resourceOrStaff.Item(resource)
    err.clear
    Dim nodX As Node ' Set temporary node variable
    ' Make sure it is not already in TreeView
    If FindNode(bookingChildNodeKey, nodX) = False Then
        'child key parameter passed in as string "resource_appType"
        If resourceOrStaffB Then ' this is a Resource so Display AppType and Times in Root.
            Set nodX = TreeView.nodes.Add(resource, tvwChild, bookingChildNodeKey, bookingChildNodeText)
        Else ' this is a Staff Member so Display AppType in Root and Times with Child.
            Set nodX = TreeView.nodes.Add(resource, tvwChild, bookingChildNodeKey, appType)
        End If
        ' Create Image for Node
        SetStaffImage nodX, resourceOrStaffB
        'Make sure Parent is expanded.
        nodX.EnsureVisible
    End If
        Set nodX = Nothing
End Sub

Private Sub AddTreeViewGrandChildNode(ByVal Key As String, ByVal appDate As Date, ByVal resource As String, _
    ByVal cardNumber As String, ByVal startTime As Date, ByVal endTime As Date, _
    ByVal appType As String, ByVal MemberNumber As String, ByVal MemberType As String, _
    ByVal firstname As String, ByVal lastname As String, ByVal makeVisible As Boolean, _
    ByVal toDelete As Boolean, ByVal appRS As Object)
    
    On Error Resume Next
    
    Dim nodX As Node
    Dim labelResource As String
    Dim labelStaff As String
    
    'Set unique key for new Child Node as there can be Multiple Resource with one instance of appType.
    Dim resourceAndAppType As Strin
    Dim resourceOrStaffB As Boolean
    ' may not be a current resource anymore False = Staff / True = Resource
    resourceOrStaffB = resourceOrStaff.Item(resource)
    err.clear
    
    'Set value to Child Key so we can find appropriate Child Node.
    resourceAndAppType = resource & "_" & appType
    ' Create Text for GrandChild Node for both Resource or Staff.
    labelResource = firstname & " " & lastname & " - " & cardNumber
    labelStaff = appUtil.appTimeFormatStr(startTime) & " - " & appUtil.appTimeFormatStr(endTime) & " | " & labelResource
    If resourceOrStaffB Then ' this is a Resource so Display only Name and Cardnumber.
        Set nodX = TreeView.nodes.Add(resourceAndAppType, tvwChild, Key, labelResource)
    Else ' this is a Staff Member so Display Name, Cardnumber and Times.
        Set nodX = TreeView.nodes.Add(resourceAndAppType, tvwChild, Key, labelStaff)
    End If
    ' Create Image for node
    SetStaffImage nodX, resourceOrStaffB

    Set nodX = Nothing
End Sub

Private Function FindNode(Key As String, nodX As Node) As Boolean
    Dim i As Integer
    ' Preset to False
    FindNode = False
    On Error Resume Next
    
    If Key <> "" Then
        ' Set nodX to Key and see if it exists already
        Set nodX = TreeView.nodes.Item(Key)
        If err.Number = 0 Then
            ' if already there return True.
            FindNode = True
        Else
            ' if not found clear any error.
            err.clear
        End If        

    End If
End Function

Open in new window

0
 

Author Closing Comment

by:FLOG51
Comment Utility
Worked it out myself so I suppose I get the points.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now