Displaying Data in Tree View and List View Controls

One powerful feature of Access development is the capability to add an ActiveX control to a form or report. ActiveX controls are additional components that provide their own user interface, properties, and methods. Adding a control such as TreeView, ListView, or Calendar is a great way to add functionality to your applications with minimal effort on your part. ActiveX controls are not without cost, however. They must be installed on each computer to use them. In many cases, this means you must have a license to distribute a particular control to other computers.

The TreeView control is used to display data that exists within a hierarchy of information. A hierarchy can involve any data contained within a one-to-one or one-to-many relationship. For example, in the Northwind database, Customers have Orders, and Orders have line items that are known as Order Details. In this section, you'll see how to add a TreeView control to a form and how to populate and display the information based on user interaction with it. You'll also take a look at the ListView control, and see an example of responding to an event in the TreeView control to fill the ListView control.

Let's say that you are tracking students and classes for a small college. Classes at the school are categorized into departments. In this example, you display the departments along with their classes in a TreeView control, and the students in a given class in a ListView control.

To begin, create a new blank form. The form does not need to be bound to a table or query because you will populate data in the TreeView control. Once you create the form, click the Insert ActiveX Control button on the Ribbon. The Insert ActiveX Control dialog box displays, as shown in Figure 10-27. For this example, you use the TreeView and ListView controls from Visual Basic 6, SP 6. Name the controls tvwClasses and lvwStudents respectively.

Insert ActiveX Control

Select an AdiueX Control:

Insert ActiveX Control

Select an AdiueX Control:

Microsoft Terminal Services Client Control (redist)

*

Microsoft Terminal Services Client Control (redist)

Microsoft Terminal Services Client Control (redist)

Microsoft Terminal Services Control

MicrosoftTooibar Control, Version 5.0 (SP2)

Microsoft Toolbar Control, version 6.0

Microsoft TreeView Control, version 5.0 (SP 2)

Microsoft TreeView Control, version 6.0

Microsoft UpDown Control 6.0 (SP4)

3

Microsoft Visio Document

Microsoft Visual Studio Tools for Office Runtime Storage Component

Microsoft Web Browser

Microsoft Works Imaging Server

Microsoft.VisualStudio.OfficeTools.Controls.LlnmanagedWrapper.dll

MMC ImnControl dass

Inserts a new Microsoft TreeView Control, verson 6.0 Into your document.

Result

Inserts a new Microsoft TreeView Control, verson 6.0 Into your document.

Figure 10-27

In the dialog box, select the desired control to add it to the form. ActiveX controls have properties that are displayed in the standard Access properties window, but many also have another set of custom properties that were added by the author of the control. Figure 10-28 shows the custom property dialog box for the TreeView control.

Excel Vba Listview
Figure 10-28

You can include images in a TreeView or ListView control by using another ActiveX control called ImageList. ImageList is a storage area for pictures that the TreeView uses when it displays the data. You won't use images in this example.

The next step is to populate the data in the TreeView control. Each item of data in a TreeView is called a node and is stored in the TreeView Nodes collection. The following code demonstrates the addition of a data node to the collection of TreeView nodes:

tvx.Nodes.Add Key:="C105", Text:="Acme Supply Co.", Image:= 1, SelectedImage:=2

The node is defined with a unique key of C105, a display name of Acme Supply Co., and is set to use the image with the key of 1 by default, and the image with the key of 2 when selected. If you use images, the image indexes are defined in a corresponding ImageList control.

The Key property of every node is of type Text, it must start with a letter, and it must be unique.

Filling the TreeView Control

During the Form_Load event procedure in this example, the TreeView is populated with one node for every department in the Departments table, along with the classes that are in the department. Here's how. First you declare object variables for the TreeView and ListView controls. You use these objects to work with the controls on the form.

Dim objTree

As

TreeView

Dim objList

As

ListView

Dim objItem

As

ListItem

Dim objParentNode

As

Node

Dim objChildNode

As

Node

Next, create the Form_Load event procedure and add the following code to use the controls on the form and provide some formatting.

Private Sub Form_Load() ' fill the treeview Dim rsDept As DAO.Recordset Dim rsClasses As DAO.Recordset

' get the controls

Set objTree = Me.tvwClasses.Object Set objList = Me.lvwStudents.Object

' format the controls With objTree.Font .Size = 9

.Name = "Segoe UI" End With

With objList.Font .Size = 9

.Name = "Segoe UI" End With

Note the use of the .Object property. It is defined by Access to return the object for the control. This property enables you to use early binding for the object model in an ActiveX control. Binding works the same here as it does for other objects. Essentially, early binding loads the data when a form loads and late binding delays attaching to the data, which typically allows for a smaller recordset and takes less time to process.

If you've used the ListView or TreeView controls in the past, you may notice that some tabs are missing from the custom property dialog box for the TreeView control. Access 2007 no longer includes the components that provide the Font property sheet, which is why you added code to format the control.

Now loop through the departments and add a node to the TreeView control: ' get the departments

Set rsDept = CurrentDb.OpenRecordset( _

"SELECT * FROM tblDepartments ORDER BY Department")

' loop through the departments While (Not rsDept.EOF)

' add the department node

Set objParentNode = objTree.Nodes.Add(, , _

"DPT" & rsDept("DepartmentID"), rsDept("Department"))

Here you're adding a node for each department with a key value of DPT & rsDept("DepartmentlD"). Each node on the TreeView control needs to have a unique key value. By concatenating the value of a primary key field (DepartmentlD) with a unique abbreviation for the table (DPT), you can ensure that each node has a unique key value. You'll re-use this key later to build relationships with other nodes. For this example, tblDepartments is the parent table. The relationships are based on the primary and foreign key relationships of the tables as shown in the WHERE clause of the next snippet of code.

It's time to add the related classes. You loop through another recordset to get the classes for the current department, and add a new node that is a child of objParentNode by specifying the tvwChild relationship argument to the Nodes.Add method:

' get the classes in the selected department Set rsClasses = CurrentDb.OpenRecordset( _

"SELECT * FROM tblClasses WHERE Department = " & _ rsDept("DepartmentID") & " ORDER BY ClassName")

' add the classes to the treeview While (Not rsClasses.EOF)

Set objChildNode = objTree.Nodes.Add(objParentNode, tvwChild, _

"CLS" & rsClasses("ClassID"), rsClasses("ClassName")) rsClasses.MoveNext

Wend rsDept.MoveNext

Wend

The only thing left to do now is some cleanup:

rsClasses.Close rsDept.Close Set rsDept = Nothing Set rsClasses = Nothing End Sub

In this example, you're working with a small number of departments and classes so you're filling everything when the form is loaded. If you have a lot of nodes to add to the TreeView, filling all the data when the form loads might take a while. To resolve that, you might choose to load the classes for a department when a node is selected, which is known as delay loading or loading on-demand.

Along with properties and methods, ActiveX controls can also provide events such as NodeClick for the TreeView. Access does not know anything about these events, so they must be added using the Visual

Basic Editor. To delay loading the classes in the TreeView, add the following event, which will then be called when you click a node in the TreeView:

Private Sub tvwClasses_NodeClick(ByVal Node As Object) Dim objNode As Node

' get the node object Set objNode = Node

If (objNode.Children = 0 And objNode.Parent Is Nothing) Then

ShowClasses Node End If

End Sub

This code makes sure that the selected node does not already have any children. It also checks the Parent property of the Node object to make sure that classes are added only when a department is selected. For root or parent nodes that do not have a parent node of their own, the Parent property will be set to Nothing.

Here's the ShowClasses procedure:

Private Sub ShowClasses(pNode As Node) Dim rsClasses As DAO.Recordset Dim lngDept As Long

' parse the department ID from the selected node lngDept = CLng(Mid(pNode.Key, 4))

' get the classes in the selected department Set rsClasses = CurrentDb.OpenRecordset( _

"SELECT * FROM tblClasses WHERE Department = " & _ lngDept & " ORDER BY ClassName")

' add the classes to the treeview While (Not rsClasses.EOF)

Set objChildNode = objTree.Nodes.Add(pNode, tvwChild, _

"CLS" & rsClasses("ClassID"), rsClasses("ClassName")) rsClasses.MoveNext

Wend rsClasses.Close Set rsClasses = Nothing End Sub

Filling the ListView Control

Once you've filled the TreeView control, you want to display the students in a given class. To accomplish this, use the ListView control that you added to the form earlier. But first, you need to configure the ListView. Begin by opening the property sheet for the ListView control, and change the View property to lvwReport.

The values for the View property probably look familiar. That's because the list of files in a Windows Explorer window is a ListView. The Explorer's Details view is called the Report view in the ListView ActiveX control. The Report view enables you to add your own columns to the ListView control.

Now add the following columns on the Column Headers tab of the ListView property dialog box. Use the Insert Column button in the dialog box (see Figure 10-29) to do so.

Index

Text

Width

1

Class

2160.00

2

First Name

1440.00

3

Last Name

1440.00

4 Major 1440.00

4 Major 1440.00

Java Treeview And Listview Control
Figure 10-29

To fill the ListView, add the following code in the NodeClick event for the TreeView. If a department is selected, the names of all the students in all classes for that department will display. If a class is selected, the names of its students display.

Private Sub tvwClasses_NodeClick(ByVal Node As Object) Dim rstStudents As DAO.Recordset Dim objNode As Node Dim strSQL As String

Dim IngID As Long

' get the node object Set objNode = Node

' parse the ID from the node Key property IngID = CLng(Mid(objNode.Key, 4))

Earlier you saved the ID field along with a table abbreviation in the Key for the node. The preceding code parses the value of the primary key field from the Key so that you can use it in a WHERE clause to get the classes for the selected class or department. Next, you look at the key to determine whether a department or class was selected and create the appropriate SQL statement:

' get the students in the selected class or department If (objNode.Key Like "DPT*") Then strSQL = "SELECT * FROM qryStudents WHERE DepartmentID = " & IngID Elself (objNode.Key Like "CLS*") Then strSQL = "SELECT * FROM qryStudents WHERE ClassID = " & lngID End If

' open the recordset

Set rstStudents = CurrentDb.OpenRecordset(strSQL)

Time to fill the ListView. For this, use the ListItems collection of the ListView. To create an entry in the ListView, use the ListItems.Add method. This returns a ListItem object that you'll use to add ListSubItems. The Add method of the ListItems collection is used to add the first column to the ListView. Subsequent columns are added to the ListSubItems collection.

' fill the listview

With objList

.ListItems.Clear

While (Not rstStudents.EOF)

Set objItem = .ListItems.

Add(, , rstStudents("ClassName"))

objItem.ListSubItems

.Add

, , rstStudents("FirstName")

objItem.ListSubItems

.Add

, , rstStudents("LastName")

objItem.ListSubItems

.Add

, , Nz(rstStudents("Major"), "Undeclared")

rstStudents.MoveNext

Wend

End With

' cleanup

rstStudents.Close

Set rstStudents = Nothing

Set objNode = Nothing

End Sub

When this is all said and done, you should have something that looks like the form in Figure 10-30.

Although the task of creating the TreeView control is completed, many other features could be added to create a more robust tool. For example, using drill through, you could open a Students detail form when you select a student in the ListView. You could add an e-mail control using the check boxes in the ListView to facilitate e-mailing grades or sending information about an upcoming exam or lecture. You can also write code to sort the items in the ListView control. With minimal programming, adding ActiveX controls to an application can give it an extra level of professionalism and pizzazz that many users will appreciate.

Access Treeview Listview
Figure 10-30

Was this article helpful?

+3 -3

Responses

  • Angelica
    How to use listview control in ms access vba?
    8 years ago
  • isengrim clayhanger
    How to insert data from ms access into treeview?
    8 years ago
  • bertha
    How to insert data from access 2007 to listview?
    8 years ago
  • willie
    How to load data to treeview control access?
    8 years ago
  • brayden
    What is activex treeview control access 2007?
    8 years ago
  • luca
    How to use tree view in ms access 2007?
    8 years ago
  • Melanie
    Which class do i register for treeview ms access 2007?
    8 years ago
  • Eva McLean
    How to insert treeview in listview?
    8 years ago
  • nasih
    How to make the treeview display information in the listview in visual basic?
    8 years ago
  • ulla-maj
    How i insert query in listview access vba?
    7 years ago
  • roisin
    How to display data in listview in access using vba?
    6 years ago
  • Darlene
    How to add records to listview activex access 2007 vba?
    6 years ago
  • Abbondanzio Endrizzi
    How to populate a listview control vba access 2007?
    6 years ago
  • loren
    How to display additional information to treeview nodes in vb 6?
    6 years ago
  • milen sayid
    How add treeview control in ms access 2007 for a table?
    6 years ago
  • Gloriana
    How to add a picture to a treeview in access 2007?
    6 years ago

Post a comment