We help IT Professionals succeed at work.

Tree View Selection

nmilmine
nmilmine asked
on
421 Views
Last Modified: 2008-02-01
Hi everyone

I have the following code which create a tree list for me with a Faculty as the first node, Associate as second node, Programme as third node and Course as forth node.

Option Compare Database
Option Explicit
Const mcstrRootNode As String = "root"

Private Sub Form_Load()
    On Error GoTo ProcErr
   
    Call msBuildTree
    Call msExpandLvlOne

ProcExit:
    On Error Resume Next
    Exit Sub
    ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ProcErr:
    MsgBox Err.Number & " " & Err.Description, vbInformation, "Form load error."
    'End Error handling block.
    Resume ProcExit
End Sub

Private Sub cboAssociateName_AfterUpdate()
 
    Me!TV1.Nodes.Clear
    Call msBuildTree
    Call msExpandLvlOne

End Sub

Private Sub msBuildTree()
    On Error GoTo ProcErr
    Dim rs As DAO.Recordset
    Dim Sql As String
    Dim Db As Database
    Set Db = CurrentDb()
    Dim ctltree As Control
    Dim NodeA As Node
    Set ctltree = Me.TV1
   
    Sql = "SELECT 'E' & tblAssociate.AssociateID AS E, tblAssociate.AssociateName AS AN, " & _
        "IIf(Not IsNull(tblAssociateProgramme.AssociateProgID),'C1' & tblAssociateProgramme.AssociateProgID,'NC' & tblAssociate.AssociateID) AS C1, " & _
        "IIf(Not IsNull(tblAssociateProgramme.AssociateProgID),tblAssociateProgramme.ProgrammeTitle,'No Programmes') AS C2, " & _
        "IIf(Not IsNull(tblAssociateProgrammeCourse.AssociateProgCourseID),'L1' & AssociateProgCourseID,'NI' & tblAssociateProgramme.AssociateProgID) AS L1, " & _
        "IIf(Not IsNull(tblAssociateProgrammeCourse.AssociateProgCourseID),CourseName,'No Courses') AS L2 " & _
        "FROM (tblAssociate LEFT JOIN tblAssociateProgramme ON tblAssociate.AssociateID = tblAssociateProgramme.AssociateID) " & _
        "LEFT JOIN tblAssociateProgrammeCourse ON tblAssociateProgramme.AssociateProgID = tblAssociateProgrammeCourse.AssociateProgID " & _
        "WHERE tblAssociate.FacultyID = " & Me!cboAssociateName.Column(0) & " " & _
        "ORDER BY tblAssociate.AssociateName, tblAssociateProgramme.ProgrammeTitle, tblAssociateProgrammeCourse.CourseName;"

    Set NodeA = ctltree.Nodes.Add(, , mcstrRootNode, Me.cboAssociateName.Column(1), 1, 2)
    Set rs = Db.OpenRecordset(Sql, dbOpenSnapshot)
   
    If rs.RecordCount = 0 Then
        rs.Close
        Set rs = Nothing
        GoTo ProcExit
    End If
   
    Do Until rs.EOF
        Set NodeA = ctltree.Nodes.Add(mcstrRootNode, 4, CStr(rs!E), rs!AN, 7, 7)
        Set NodeA = ctltree.Nodes.Add(CStr(rs!E), 4, CStr(rs!C1), rs!C2, 3, 3)
        Set NodeA = ctltree.Nodes.Add(CStr(rs!C1), 4, CStr(rs!L1), rs!L2, 8, 8)
        rs.MoveNext
    Loop

ProcExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set Db = Nothing
    Exit Sub

ProcErr:
    If Err.Number = 35602 Then Resume Next
        MsgBox Err.Number & " " & Err.Description, vbInformation, "Build tree error."
    Resume Next

End Sub

Private Sub msExpandLvlOne()
    On Error GoTo ProcErr
    Dim intCount As Integer
    Dim ctltree As Control
    Dim strNodeParent As String
    intCount = 0
    Set ctltree = Me.TV1
    ctltree.Nodes(1).Expanded = True
    For intCount = 2 To ctltree.Nodes.Count
        strNodeParent = ctltree.Nodes(intCount).Parent
        If strNodeParent = mcstrRootNode Then
            ctltree.Nodes(intCount).Expanded = True
        End If
    Next intCount

ProcExit:
    On Error Resume Next
    Exit Sub

ProcErr:
    MsgBox Err.Number & " " & Err.Description, vbInformation, "Expand level one error."
    Resume Next

End Sub

This seems to be working although I have little knowledge of how tree view works

What I would like to happen is when someone clicks on the Associate name that it unhides a form called frmAssociate displaying the record for the AssociateName selected.  Similarily I would like the same to happen for Programme (frmAssociateProgrammes) and Course (frmAssciateProgrammeCourse)

The PK for the tables are
  tblAssociate = AssociateID
  tblAssociateProgramme = AssociateProgID
  tblAssociateProgrammeCourse = AssociateProgCourseID

Thanks
Neil
Comment
Watch Question

Commented:
Hmm, I do have a sample in dutch when you're interested drop me a mail.

Basically I guess all you need is to handle the double_click event of a node and then:
txtSelectedText = Treeview1.SelectedItem.Text
txtSelectedKey = Treeview1.SelectedItem.Key

Thus you get the text and Key and depending on how this is constructed you can issue the needed docmd.openform command.
When you use e.g. a coded key in the treeview like:
A/12345 for associate
P/12345 for associate program and
C/12345 for the course
you can use a select case for activating the correct form.

Clear ?

Nic;o)

Author

Commented:
Hi Nic

Have found the following which is working apart from the filtering to the correct record

Private Sub TV1_NodeClick(ByVal Node As Object)

Dim strNodeKey As String
Dim SubfrmAssociate As Form
Dim SubfrmAssociateProgramme As Form
Dim SubfrmAssociateProgrammeCourse As Form
strNodeKey = Left(Node.Key, 1)

'MsgBox "Node Key " & Right(Len(Node.Key), Len(Node.Key) - 1)

Select Case strNodeKey
   
    Case "E"
        Me!SubfrmAssociate.Visible = True
        Me!SubfrmAssociateProgramme.Visible = False
        Me!SubfrmAssociateProgrammeCourse.Visible = False
        Me!SubfrmAssociate.Form.FilterOn = False
        Me!SubfrmAssociate.Form.Filter = "[AssociateID] = " & Right(Len(Node.Key), Len(Node.Key) - 1)
        Me!SubfrmAssociate.Form.FilterOn = True
   
    Case "C"
        Me!SubfrmAssociate.Visible = False
        Me!SubfrmAssociateProgramme.Visible = True
        Me!SubfrmAssociateProgrammeCourse.Visible = False
        Me!SubfrmAssociateProgramme.Form.FilterOn = False
        Me!SubfrmAssociateProgramme.Form.Filter = "[AssociateProgID] = " & Right(Len(Node.Key), Len(Node.Key) - 1)
        Me!SubfrmAssociateProgramme.Form.FilterOn = True
   
    Case "L"
        Me.SubfrmAssociate.Visible = False
        Me.SubfrmAssociateProgramme.Visible = False
        Me.SubfrmAssociateProgrammeCourse.Visible = True
        Me!SubfrmAssociateProgrammeCourse.Form.FilterOn = False
        Me!SubfrmAssociateProgrammeCourse.Form.Filter = "[AssociateProgCourseID] = " & Right(Len(Node.Key), Len(Node.Key) - 1)
        Me!SubfrmAssociateProgrammeCourse.Form.FilterOn = True

End Select

End Sub

Regards
Neil

Commented:
Hmm, I guess you need to use:
Mid(Node.Key,2)
to extract the key value.

Nic;o)

Author

Commented:
Hi Nic

Have managed to get it to work using

Private Sub TV1_NodeClick(ByVal Node As Object)

Dim strNodeKey As String
Dim NSelect As String
Dim SubfrmAssociate As Form
Dim SubfrmAssociateProgramme As Form
Dim SubfrmAssociateProgrammeCourse As Form

strNodeKey = Left(Node.Key, 1)

NSelect = Right(Node.Key, Len(Node.Key) - 1)

Select Case strNodeKey
   
    Case "E"
        Me!SubfrmAssociate.Visible = True
        Me!SubfrmAssociateProgramme.Visible = False
        Me!SubfrmAssociateProgrammeCourse.Visible = False
        Me!SubfrmAssociate.Form.FilterOn = False
        Me!SubfrmAssociate.Form.Filter = "[AssociateID] = " & NSelect
        Me!SubfrmAssociate.Form.FilterOn = True
        Me!txtStatus1 = "Associate: " & Node.Text
   
    Case "C"
        Me!SubfrmAssociate.Visible = False
        Me!SubfrmAssociateProgramme.Visible = True
        Me!SubfrmAssociateProgrammeCourse.Visible = False
        Me!SubfrmAssociateProgramme.Form.FilterOn = False
        Me!SubfrmAssociateProgramme.Form.Filter = "[AssociateProgID] = " & NSelect
        Me!SubfrmAssociateProgramme.Form.FilterOn = True
        Me!txtStatus1 = "Programme: " & Node.Text
   
    Case "L"
        Me.SubfrmAssociate.Visible = False
        Me.SubfrmAssociateProgramme.Visible = False
        Me.SubfrmAssociateProgrammeCourse.Visible = True
        Me!SubfrmAssociateProgrammeCourse.Form.FilterOn = False
        Me!SubfrmAssociateProgrammeCourse.Form.Filter = "[AssociateProgCourseID] = " & NSelect
        Me!SubfrmAssociateProgrammeCourse.Form.FilterOn = True
        Me!txtStatus1 = "Course: " & Node.Text

    Case Else
        Me.SubfrmAssociate.Visible = False
        Me.SubfrmAssociateProgramme.Visible = False
        Me.SubfrmAssociateProgrammeCourse.Visible = False
        Me!txtStatus1 = " "

End Select

End Sub

Is it OK by you for me to delete the question

Regards
Neil
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Will Mid work if data is
  E1 or E10 or E1000

Cheers
Neil

Commented:
Yes, when there's no length parameter the remaining part will be taken regardless the length.
Don't take my word for it, just try :-)

Nic;o)

Author

Commented:
Thanks Nic

Neil

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.