Share via

apply a filter a treeview control

Anonymous
2011-07-06T10:24:43+00:00

Hello

I am using a Treeview control that was kindly supplied by Mr HanV.

The treeview has dates, classes and students like this

12/12/2012

Class 1

John Smith

Sally Smith

Class 2

Bradd Pitt

Julia Roberts

12/12/2012

Class 1

Mickie Mouse

Minny Mouse

Class 2

Graham Bell

etc

The treeview is based on a query

When filtering a form I can just add a query by form (Forms![FormName]![ControlName] Or Forms![FormName]![ControlName] Is Null)

and this will filter the form or, if nothing is selected, show all records.

But with a treeview this does not work.

I would like to put a combo box on the form with the treeview control (the combo would have dates) and then use this to filter the date shown on the treeview. (filter the query the treeview is based on)

Is this possible for someone with limited experience

Thank you

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-07-06T14:32:44+00:00

    Because we now want to fill the treeview when the form is loaded and also when an item is selected in the combo box, I split off the routine to fill the treeview:

    Private Sub FillTreeView()

        Dim dbs As DAO.Database

        Dim rst1 As DAO.Recordset

        Dim rst2 As DAO.Recordset

        Dim rst3 As DAO.Recordset

        Dim nod1 As MSComctlLib.Node

        Dim nod2 As MSComctlLib.Node

        Dim strSQL As String

        Me.TreeView0.Nodes.Clear

        Set dbs = CurrentDb

        strSQL = "SELECT DISTINCT StartDate FROM tblClasses"

        If Not IsNull(Me.cboDate) Then

            strSQL = strSQL & " WHERE StartDate=#" & Format(Me.cboDate, "mm/dd/yyyy") & "#"

        End If

        Set rst1 = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

        Do While Not rst1.EOF

            Set nod1 = Me.TreeView0.Nodes.Add(Key:="D" & rst1!StartDate, _

                Text:="Date " & rst1!StartDate)

            Set rst2 = dbs.OpenRecordset("SELECT ClassID, ClassNumber FROM tblClasses WHERE StartDate=#" & _

                Format(rst1!StartDate, "mm/dd/yyyy") & "# ORDER BY ClassID", dbOpenForwardOnly)

            Do While Not rst2.EOF

                Set nod2 = Me.TreeView0.Nodes.Add(Relative:=nod1, Relationship:=tvwChild, _

                    Key:="C" & rst2!ClassID, _

                    Text:="Class " & rst2!ClassNumber)

                Set rst3 = dbs.OpenRecordset("SELECT StudentID, StudentFullName FROM tblStudents WHERE StudentClassID=" & _

                    rst2!ClassID & " ORDER BY StudentFullName", dbOpenForwardOnly)

                Do While Not rst3.EOF

                    Me.TreeView0.Nodes.Add Relative:=nod2, Relationship:=tvwChild, _

                        Key:="M" & rst3!StudentID, Text:=rst3!StudentFullName

                  rst3.MoveNext

                Loop

                rst3.Close

                rst2.MoveNext

            Loop

            rst2.Close

            rst1.MoveNext

        Loop

        ' Optional - expand treeview

        For Each nod1 In Me.TreeView0.Nodes

            nod1.Expanded = True

        Next nod1

        Set rst3 = Nothing

        Set rst2 = Nothing

        rst1.Close

        Set rst1 = Nothing

        Set dbs = Nothing

    End Sub

    We can then call it where needed:

    Private Sub cboDate_AfterUpdate()

        FillTreeView

    End Sub

    Private Sub Form_Load()

        FillTreeView

    End Sub

    where cboDate is the name of the combo box. With the above code, you don't have to use a query for the treeview - the filtering is done within the code.

    The sample database at https://skydrive.live.com/#!/?cid=0cae9ff0a56dd9d4&sc=documents&uc=1&id=CAE9FF0A56DD9D4%21112 has been updated.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-07-08T16:34:56+00:00

    I have tried to learn some of the codes and methods from the treeviews you kindly supplied and I am just starting to work on the next one.

    All of the administrative assistants (everyone here works without wages) were really impressed with how much simpler it was to use than my old system which involved lots of combo boxes on the same form. 

    This next one will be slightly more complex and I am enjoying learning about how the treeviews work. I have been following the lessons shown here

    http://mymsaccessblog.blogspot.com/2008/02/my-treeview-project-episode-1-hello.html

    The next treeview I will create is slightly more complex as each student will take 2 of the child nodes.  In the treeview you supplied each student could go into one class and one class only but the next will involve the allocation of transport.

    Our non-physically able students are supplied with transport to and from this facility from their home.  So I think I have some work to do.  I have told everyone that it should be up and running by Christmas (possibly). LoL.

    This will be a many to many relationship Students – busses, as they can take (up to) 2 trips per day and many people can get on each bus and sometimes the students will get on different buses depending upon their transport requirements.

    I will spend sometime to ensure the tables and queries are set up correctly before going into the form/treeview.

    Thanks for your previous help.

    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-07-08T14:53:50+00:00

    You're welcome! TreeViews are fun, but daunting if you've never used them...

    0 comments
  3. Anonymous
    2011-07-08T14:28:39+00:00

    I just wanted to say and enormous thank you for your assistance with the Treeview which I simply would not have been able to do without your help.

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-07-06T11:57:19+00:00

    Currently, you have code that populates the treeview when the form is loaded.

    Basically, you'd have to clear the nodes of the treeview and repopulate it in the After Update event of the combo box.

    I'll try to post code or upload an example in a few hours (no time right now).

    0 comments