Home

List-Based Windows Controls: The List Box

Introduction to List Views

A Review of Windows Controls and Record Sets

Record sets are at the heart of database programming, especially in Microsoft Access. You can get a record set from an existing object (table, query, form, or report) or create one from selecting records. You can then perform various types of operations.

Introduction to the List View

A list view is a list of items so that the list can be presented in a certain view.:

  • The items can each appear with a large icon and a label
  • The items can each appear with a small icon and a label
  • The items can be made to show some details (related-information) each

Creating a List View

In Microsoft Access, a list view is one of the controls that are not immediately available when the application starts. A list view is provided as an ActiveX control. To get it, first display a form or a report in the Design View. In the Controls section of the Ribbon, click the More button and click ActiveX Controls. Scroll down in the list box and select Microsoft ListView Control:

ActiveX Controls - Microsoft Tree View Control

Click OK. After doing this, a white rectangle is added to your form or report. You can then manage the list view. First, you should give it a meaning name in the Property Sheet or the Properties window.

The list view is based on a class named ListView. After adding a list view to your form or report, to programmatically create a list view, declare a variable of that class and initialize it. Here is an example:

Private Sub cmdListView_Click()
    Dim lvAutoParts As ListView
    
    Set lvAutoParts = New ListView
End Sub

Introduction to the Items of a List View

The Collection of List View Items

To support the items of a list view, the ListView class is equipped with a property named ListItems, which is a collection. Each item of a list view, that is, every member of the ListView.ListItems collection is based on a class named ListItem.

Creating an Item

To let you create an item in a list view, the ListView.ListItems collection contains a method named Add. Its syntax is:

Public Function Add(ByVal Optional index As Variant,
		    ByVal Optional key As Integer,
		    ByVal Optional text As String,
		    ByVal Optional icon As Integer,
		    ByVal Optional smallIcon As Integer) As ListItem

All arguments are optional. If you call this method without an argument, an empty item would be created and you will not receive an error. Otherwise, to create an item, pass just the third argument. Here is an example:

Private Sub Form_Load()
    lvEmployees.ListItems.Add , , "James"
End Sub

This would produce:

List View - Creating an Item

In the same way, you can create as many items as you want. Here are examples:

Private Sub Form_Load()
    lvEmployees.ListItems.Add , , "James"
    lvEmployees.ListItems.Add , , "Cavani"
    lvEmployees.ListItems.Add , , "Stern"
    lvEmployees.ListItems.Add , , "Hanides"
End Sub

This would produce:

List View - Creating List View Items

The ListView.ListItems.Add() method returns a ListItem object. If you are planing to refer to an item after creating it, you should get its reference. This is done by calling the method as a function and assigning its return value to a variable of type ListView. This can be done as follows:

Private Sub cmdListView_Click()
    Dim lviAutoPart As ListItem
    
    Set lviAutoPart = lvAutoParts.ListItems.Add(, , "Buick")
End Sub

It is recommended that you always get a reference to the item you are creating even if you are not planning to use that reference. Here are examples:

Private Sub cmdListView_Click()
    Dim lviAutoPart As ListItem
    
    Set lviAutoPart = lvAutoParts.ListItems.Add(, , "Buick")
    Set lviAutoPart = lvAutoParts.ListItems.Add(, , "Toyota")
    Set lviAutoPart = lvAutoParts.ListItems.Add(, , "Ford")
    Set lviAutoPart = lvAutoParts.ListItems.Add(, , "BMW")
End Sub

The Styles of a List View

Introduction

A list view provides various options to display its items. To support this, the ListView class is equipped with the View property that uses some contant values that are:

Constant Value Description
lvwIcon 0 Large Icons Style: In this view, the control displays a list of items in the order they were added from left to right and from top to bottom. This means that items start displaying on the top left section of the control to right
lvwSmallIcon 1 Small Icons Style: Like the Large Icons style, this view displays the list of items from the left to the right then to the next line if necessary
lvwList 2 List Style: This list is arranged in columns: the first item appears to the left side of the control. The next item (usually in alphabetical order) appears under it, and so on. If there are more items to fit in one column, the list continues with a new column to the right of the previous one
lvwReport 3 Details or Reports Style: Instead of showing just the string of the (main) item, each item can provide more detailed information in a column

Here is an example of specifying the style of a list view:

Private Sub cmdFindReceipt_Click()
    
    lvSoldItems.View = lvwReport

End Sub

The Icons of List View Items

A list view has built-in capability to display icons. Before using the pictures, you should store them in image lists. Each set must be stored in its own ImageList object.

To support the various sets of icons, the ListView class is equipped with a property named ListImages.

Visually Configuring a List View

To visually configure a list view, display its hosting form or report in the Design View. Double-click the list view. This would display the Properties dialog box of the list view:

ActiveX Controls - Microsoft Tree View Control

The Report/Detail Version of a List View

Introduction

A list view can be made to display detailed information about each item. Such a list view is organized in sections referred to as columns.

To support columns, the ListView class is equipped with a property named ColumnHeaders, which is a collection. Each column, that is every member of the ColumnHeaders collection, is based on a class named ColumnHeader.

To let you create a column, the ColumnHeaders collection is equipped with a method named Add. Its syntax is:

Public Function Add(ByVal Optional index As Integer,
		    ByVal Optional key As String,
		    ByVal Optional text As String,
		    ByVal Optional width As Integer,
		    ByVal Optional alignment As Alignment,
		    ByVal Optional icon As Integer) As ColumnHeader

The first argument is a natural number that uniquely identifies the new column among the other columns. The columns are numbered as 1, 2, 3, and so on. You should pass this argument only if you want to explicitly specify the index of the column you are adding. If you don't pass this argument, at all on all calls of this method, the first column will receive an index of 1, the second column will have an index of 2, and so on. Otherwise, you can pass an index of your choice. Here is an example:

Private Sub Form_Load()
    lvEmployees.View = lvwReport

    lvEmployees.ColumnHeaders.Add
    lvEmployees.ColumnHeaders.Add 1
End Sub

This would produce:

List View - Report

The ColumnHeaders.Add() method returns an object of type ColumnHeader. If you plan to use a reference to the column after creating the column, you should get that return value.

Remember that each column index must be unique. After creating the column, to let you get the index of a column, the ColumnHeader class is equipped with a read-only property named Index.

The second argument of the ColumnHeaders.Add() method is a name that uniquely identifies the new item among the others. The name can be anything you want. It can contain space and special characters. If you don't specify this argument or you want to change the key of a column, to let you access the key of a column, the ColumnHeader class is equipped with a property named Key. Remember that you can use it to specify the unique key of a column or to change the key of a column. Here is an example:

Private Sub Form_Load()
    Dim colEmployee As ColumnHeader
    
    lvEmployees.View = lvwReport

    Set colEmployee = lvEmployees.ColumnHeaders.Add(, "EmployeeNumber")
    Set colEmployee = lvEmployees.ColumnHeaders.Add(1)
    colEmployee.Key = "FirstName"
End Sub

The third argument is the caption (or title) of the column, that is, the text that will show in the top portion of the column. If you don't pass this argumentor or you want to change the caption of a column, to let you access the text of a column, the ColumnHeader class is equipped with a property named Text. You can use it to specify the unique key of a column or to change the key of a column. Here are examples:

Private Sub Form_Load()
    lvEmployees.View = lvwReport

    lvEmployees.ColumnHeaders.Add 1, "EmployeeNumber", "Employee #"
    lvEmployees.ColumnHeaders.Add 2, "FirstName", "First Name"
    lvEmployees.ColumnHeaders.Add 3, "LastName", "Last Name"
    lvEmployees.ColumnHeaders.Add 4, "Title", "Title"
End Sub

The fourth argument is the numeric width allocated to the whole column. Here are examples of specifying it:

Private Sub Form_Load()
    lvEmployees.View = lvwReport

    lvEmployees.ColumnHeaders.Add 1, "EmployeeNumber", "Employee #", 750
    lvEmployees.ColumnHeaders.Add 2, "FirstName", "First Name", 900
    lvEmployees.ColumnHeaders.Add 3, "LastName", "Last Name", 900
    lvEmployees.ColumnHeaders.Add 4, "Title", "Title", 1500
    lvEmployees.ColumnHeaders.Add 5, "Sex", "Gender", 550
    lvEmployees.ColumnHeaders.Add 6, "YearlySalary", "Salary", 650
End Sub

This fifth column specifies how the caption will be aligned, to the left (the default), the center, or the right side. This is relative to the width. The available values are:

Constant Value Description
lvwColumnLeft 0 The caption of the column will align to the left side. Thid is the default
lvwColumnRight 1 The caption of the column will align to the right side
lvwColumnCenter 2 The caption of the column will align to the center

Here are examples of specifying this option:

Private Sub Form_Load()
    lvEmployees.View = lvwReport
    
    lvEmployees.ColumnHeaders.Add 1, "EmployeeNumber", "Employee #", 750
    lvEmployees.ColumnHeaders.Add 2, "FirstName", "First Name", 900
    lvEmployees.ColumnHeaders.Add 3, "LastName", "Last Name", 900
    lvEmployees.ColumnHeaders.Add 4, "Title", "Title", 1500
    lvEmployees.ColumnHeaders.Add 5, "Sex", "Gender", 550, lvwColumnCenter
    lvEmployees.ColumnHeaders.Add 6, "YearlySalary", "Salary", 650, lvwColumnRight
End Sub

This would produce:

List View - Column Value Alignment

The last argument represents the index of the icon.

The Number of Columns of a List View

As reviewed above, the columns of a list view are stored in a collection. To know the number of columns of a list view, you can check its Count property.

Deleting Columns

If you don't need a column any more, you can delete it. In the same way, you can delete all columns of a list view. To let you delete a ColumnHeader object, the ListView.ColumnHeaders collection is equipped with a method named Remove. Its syntax is:

Public Sub Remove(ByVal index As Integer)

This method takes an argument as the index of the column to delete. To let you delete all columns of a list view, the ListView.ColumnHeaders collection is equipped with a method named Clear. Its syntax is:

Public Sub Clear

The Sub-Items of an Item

The idea of having columns is to provide more information about each item of a list view instead of a simple string for each. A sub-item is an item created as a child of an existing item.

To support sub-items, the ListItem class is equipped with a property named SubItems, which is a collection. To let you create a sub-item, the ListItem.SubItems collection is equipped with the Add() method. Its syntax is:

Public Function Add(ByVal Optional index As Integer,
		    ByVal Optional key As String,
		    ByVal Optional text As String,
		    ByVal Optional icon As Integer,
		    ByVal Optional smallIcon As Integer) As ListItem

All arguments are optional. The first argument is the integral position where you want to put the new argument. If you don't pass this argument, if this is not the first item, it will be added after the previous one. The second argument is a unique name for the new item. The third argument is the caption of the new item. If you omit the third argument, you can as well assign a string to the item. Here are examples of creating list view items and their sub-items:

Private Sub Form_Load()
    Dim lviEmployee As ListItem
    Dim colEmployee As ColumnHeader
    
    lvEmployees.View = lvwReport
    
    Set colEmployee = lvEmployees.ColumnHeaders.Add(1, "EmployeeNumber", "Employee #", 1050)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(2, "FirstName", "First Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(3, "LastName", "Last Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(4, "Title", "Title", 1650)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(5, "Sex", "Gender", 750, lvwColumnCenter)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(6, "YearlySalary", "Salary", 750, lvwColumnRight)

    Set lviEmployee = lvEmployees.ListItems.Add(, , "273974")
    lviEmployee.SubItems(1) = "Robert"
    lviEmployee.SubItems(2) = "James"
    lviEmployee.SubItems(3) = "General Manager"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 78480
     Set lviEmployee = lvEmployees.ListItems.Add(, , "979-407")
    
    lviEmployee.SubItems(1) = "Jennifer"
    lviEmployee.SubItems(2) = "Cavani"
    lviEmployee.SubItems(3) = "Webmaster"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 57575
    Set lviEmployee = lvEmployees.ListItems.Add(, , "584-931")
    lviEmployee.SubItems(1) = "Christine"
    lviEmployee.SubItems(2) = "Stern"
    lviEmployee.SubItems(3) = "Clerk"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 646225
    Set lviEmployee = lvEmployees.ListItems.Add(, , "739-712")
    lviEmployee.SubItems(1) = "Harry"
    lviEmployee.SubItems(2) = "Hanides"
    lviEmployee.SubItems(3) = "Tester"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 68495
End Sub

This would produce:

List View - Sub-Items

The Grid Lines of a List View

To visually delimit the rows of the list view, you can make it display grid lines. To do this visually, access the Properties dialog box of the list view and check the Gridlines check box. To set this programmatically, access the GridLines property of the list view and set it to True. Here is an example:

Private Sub Form_Load()
    Dim lviEmployee As ListItem
    Dim colEmployee As ColumnHeader
    
    lvEmployees.View = lvwReport
    lvEmployees.GridLines = True
    
    Set colEmployee = lvEmployees.ColumnHeaders.Add(1, "EmployeeNumber", "Employee #", 1050)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(2, "FirstName", "First Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(3, "LastName", "Last Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(4, "Title", "Title", 1650)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(5, "Sex", "Gender", 750, lvwColumnCenter)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(6, "YearlySalary", "Salary", 750, lvwColumnRight)

    Set lviEmployee = lvEmployees.ListItems.Add(, , "273974")
    lviEmployee.SubItems(1) = "Robert"
    lviEmployee.SubItems(2) = "James"
    lviEmployee.SubItems(3) = "General Manager"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 78480
     Set lviEmployee = lvEmployees.ListItems.Add(, , "979-407")
    
    lviEmployee.SubItems(1) = "Jennifer"
    lviEmployee.SubItems(2) = "Cavani"
    lviEmployee.SubItems(3) = "Webmaster"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 57575
    Set lviEmployee = lvEmployees.ListItems.Add(, , "584-931")
    lviEmployee.SubItems(1) = "Christine"
    lviEmployee.SubItems(2) = "Stern"
    lviEmployee.SubItems(3) = "Clerk"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 646225
    Set lviEmployee = lvEmployees.ListItems.Add(, , "739-712")
    lviEmployee.SubItems(1) = "Harry"
    lviEmployee.SubItems(2) = "Hanides"
    lviEmployee.SubItems(3) = "Tester"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 68495
End Sub

This would produce:

List View - The Grid Lines of a List View

Fully Selecting a Row

By Default, the user can select only the value in the first column of a list view. To allow you to select a whole row, the ListView class is equipped with a property named FullRowSelect.

To visually allows the user to select a whole row, access the Properties dialog box of the list view and check the FullRowSelect check box. To set this programmatically, access the FullRowSelect property of the list view and set it to True. Here is an example:

Private Sub Form_Load()
    Dim lviEmployee As ListItem
    Dim colEmployee As ColumnHeader
    
    lvEmployees.View = lvwReport
    lvEmployees.GridLines = True
    lvEmployees.FullRowSelect = True
    
    Set colEmployee = lvEmployees.ColumnHeaders.Add(1, "EmployeeNumber", "Employee #", 1050)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(2, "FirstName", "First Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(3, "LastName", "Last Name", 1100)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(4, "Title", "Title", 1650)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(5, "Sex", "Gender", 750, lvwColumnCenter)
    Set colEmployee = lvEmployees.ColumnHeaders.Add(6, "YearlySalary", "Salary", 750, lvwColumnRight)

    Set lviEmployee = lvEmployees.ListItems.Add(, , "273974")
    lviEmployee.SubItems(1) = "Robert"
    lviEmployee.SubItems(2) = "James"
    lviEmployee.SubItems(3) = "General Manager"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 78480
     Set lviEmployee = lvEmployees.ListItems.Add(, , "979-407")
    
    lviEmployee.SubItems(1) = "Jennifer"
    lviEmployee.SubItems(2) = "Cavani"
    lviEmployee.SubItems(3) = "Webmaster"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 57575
    Set lviEmployee = lvEmployees.ListItems.Add(, , "584-931")
    lviEmployee.SubItems(1) = "Christine"
    lviEmployee.SubItems(2) = "Stern"
    lviEmployee.SubItems(3) = "Clerk"
    lviEmployee.SubItems(4) = "F"
    lviEmployee.SubItems(5) = 646225
    Set lviEmployee = lvEmployees.ListItems.Add(, , "739-712")
    lviEmployee.SubItems(1) = "Harry"
    lviEmployee.SubItems(2) = "Hanides"
    lviEmployee.SubItems(3) = "Tester"
    lviEmployee.SubItems(4) = "M"
    lviEmployee.SubItems(5) = 68495
End Sub

This would produce:

List View - Full Row Select

Using a List View

Selecting an Item

As mentioned previously, a list view is made of a list of items. An item can be identified by its index. When a list view comes up, it displays its list of items. To use an item, the user can click it. When an item has been clicked and it becomes selected, the list view fires an event named ItemClick.

Editing a Label

Clicking an item once allows the user to select it. You may create an application that allows the user to edit an item, that is, to change the string that the item displays. To edit an item, the user can click (once) an item, then click (once) the item again. This puts it into edit mode. The user can then use the keyboard to change the string of the item.

To support the ability to let the user edit an item, the ListView class is equipped with a propertyt named LabelEdit, which if of type Integer. When the user starts editing, the control fires an event named BeforeLabelEdit. This event allows you to take care of some early processing, such as checking what the user is doing or canceling the editing. If you allow the user to edit the item, after the user has edited it, the control fires an event named AfterLabelEdit.

Using Columns

Besides the items, the user can also use the columns. When the user clicks a column header, the control fires an event named ColumnClick.

 
 
 

The Microsoft Access List Box

Introduction

In Microsoft Windows (and most operating systems), a list box is a Windows control that displays a list of items, usually in one column. Microsoft Access provides a list box that goes beyond the traditional list box. As we will see, the list box in Microsoft Access is configured to appear and behave like a list view.

ApplicationTopic Applied: Deleting Items

  1. On the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the file name as Fun Department Store5
  4. Click Create
  5. On the Ribbon, click Create and click Form Design
  6. In the Controls section of the Ribbon, click the Button.
    If a wizard starts, click Cancel
  7. While the button is selected on the form, in the Property Sheet, click the All tab and change the Name to cmdCreateTable
  8. On the form, right-click the button and click Build Event...
  9. In the Choose Builder dialog box, click Code Builder and click OK
  10. Implement the event as follows:
    Private Sub cmdCreateTable_Click()
        Dim dbFunDS As Database
        Dim rsEmployees As Recordset
        Dim rsStoreItems As Recordset
        
        Set dbFunDS = CurrentDb
        
        DoCmd.RunSQL "CREATE TABLE Employees                " & _
                     "(                                     " & _
                     "    EmployeeID     COUNTER,           " & _
                     "    EmployeeNumber Text(10),          " & _
                     "    FirstName      String(25),        " & _
                     "    LastName       Char(25) not null, " & _
                     "    Title          text(100)          " & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE StoreItems               " & _
                     "(                                     " & _
                     "  StoreItemID  AUTOINCREMENT,         " & _
                     "  ItemNumber   Text(10) NOT NULL,     " & _
                     "  Manufacturer Text(40),              " & _
                     "  Category     String(40),            " & _
                     "  SubCategory  Char(40),              " & _
                     "  ItemName     text(120) not null,    " & _
                     "  ItemSize     string(25),            " & _
                     "  UnitPrice    Double not null        " & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE ShoppingSesssions        " & _
                     "(                                     " & _
                     "  ReceiptNumber  Long,                " & _
                     "  EmployeeNumber Text(10),            " & _
                     "  ShoppingDate   String(40),          " & _
                     "  ShoppingTime   Char(20),            " & _
                     "  OrderTotal     Double NOT NULL,     " & _
                     "  AmountTendered Double,              " & _
                     "  Change         Double               " & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE SellingItems             " & _
                     "(                                     " & _
                     "  SellingItemID Counter,              " & _
                     "  ItemNumber    Text(10) NOT NULL,    " & _
                     "  Manufacturer  Text(40),             " & _
                     "  Category      String(40),           " & _
                     "  SubCategory   Char(40),             " & _
                     "  ItemName      text(120) not null,   " & _
                     "  ItemSize      string(25),           " & _
                     "  UnitPrice     Double not null       " & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE SoldItems                " & _
                     "(                                     " & _
                     "  SoldItemID     AutoIncrement,       " & _
                     "  ReceiptNumber  Long,                " & _
                     "  ItemNumber     Text(10) NOT NULL,   " & _
                     "  Manufacturer   Text(40),            " & _
                     "  Category       String(40),          " & _
                     "  SubCategory    Char(40),            " & _
                     "  ItemName       text(120) not null,  " & _
                     "  ItemSize       string(25),          " & _
                     "  UnitPrice      Double not null      " & _
                     ");"
        
        Set rsEmployees = dbFunDS.OpenRecordset("Employees", RecordsetTypeEnum.dbOpenTable, RecordsetOptionEnum.dbAppendOnly, LockTypeEnum.dbOptimistic)
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "402446": rsEmployees!FirstName = "Catherine": rsEmployees!LastName = "Watts": rsEmployees!Title = "Owner - General Manager"
        rsEmployees.Update
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "295374": rsEmployees!FirstName = "James": rsEmployees!LastName = "Levins": rsEmployees!Title = "Store Manager"
        rsEmployees.Update
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "927486": rsEmployees!FirstName = "Amanda": rsEmployees!LastName = "Aronson": rsEmployees!Title = "Cashier"
        rsEmployees.Update
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "473847": rsEmployees!FirstName = "Jeanne": rsEmployees!LastName = "Alcott": rsEmployees!Title = "Cashier"
        rsEmployees.Update
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "275594": rsEmployees!FirstName = "Jennifer": rsEmployees!LastName = "Roberts": rsEmployees!Title = "Sales Manager"
        rsEmployees.Update
        
        rsEmployees.AddNew
        rsEmployees!EmployeeNumber = "702048": rsEmployees!FirstName = "Alfred": rsEmployees!LastName = "Harrison": rsEmployees!Title = "Cashier"
        rsEmployees.Update
        
        Set rsStoreItems = dbFunDS.OpenRecordset("StoreItems", RecordsetTypeEnum.dbOpenTable, RecordsetOptionEnum.dbAppendOnly, LockTypeEnum.dbOptimistic)
        
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "239679": rsStoreItems!Manufacturer = "Lauren by Ralph Lauren": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Dresses": rsStoreItems!ItemName = "Sleeveless Shimmer Dress": rsStoreItems!ItemSize = "6": rsStoreItems!UnitPrice = 150#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "426643": rsStoreItems!Manufacturer = "Calvin Klein": rsStoreItems!Category = "Men": rsStoreItems!SubCategory = "Accessories": rsStoreItems!ItemName = "Knit Scarf": rsStoreItems!UnitPrice = 50#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "544915": rsStoreItems!Manufacturer = "Nautica": rsStoreItems!Category = "Baby Girls": rsStoreItems!SubCategory = "Baby Girls": rsStoreItems!ItemName = "12-24 Months Lace Two-Piece Set": rsStoreItems!ItemSize = "24M": rsStoreItems!UnitPrice = 40#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "345500": rsStoreItems!Manufacturer = "Calvin Klein": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Skirts": rsStoreItems!ItemName = "Petite Crossing Paths Print Skirt": rsStoreItems!ItemSize = "12": rsStoreItems!UnitPrice = 120#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "405758": rsStoreItems!Manufacturer = "Guess": rsStoreItems!Category = "Girls": rsStoreItems!SubCategory = "Dresses": rsStoreItems!ItemName = "Girls 2-6X Denim Print Ruffled Dress": rsStoreItems!ItemSize = "6-May": rsStoreItems!UnitPrice = 34.95
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "379367": rsStoreItems!Manufacturer = "Calvin Klein": rsStoreItems!Category = "Men": rsStoreItems!SubCategory = "Shoes": rsStoreItems!ItemName = "Hervey Square-Toe Leather Loafers": rsStoreItems!ItemSize = "11": rsStoreItems!UnitPrice = 100#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "769121": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Accessories": rsStoreItems!ItemName = "Two-Tone Scarf": rsStoreItems!UnitPrice = 44.75
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "388729": rsStoreItems!Manufacturer = "AK Anne Klein": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Shoes": rsStoreItems!ItemName = "Claw Buckle-Detail Wedge Sandals": rsStoreItems!ItemSize = "5": rsStoreItems!UnitPrice = 69.95
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "948596": rsStoreItems!Manufacturer = "Tommy Hilfiger": rsStoreItems!Category = "Men": rsStoreItems!SubCategory = "Shirts": rsStoreItems!ItemName = "Texture Oxford Slim Fit Long Sleeve Dress Shirt": rsStoreItems!ItemSize = "17 34/35": rsStoreItems!UnitPrice = 47.75
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "430669": rsStoreItems!Manufacturer = "Kenneth Cole New York": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Jewelry": rsStoreItems!ItemName = "Goldtone Beaded Double-Drop Earrings": rsStoreItems!ItemSize = "No Size": rsStoreItems!UnitPrice = 26.95
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "290699": rsStoreItems!Manufacturer = "Ralph Lauren": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Shoes": rsStoreItems!ItemName = "Whip Snake Print Perfect Pumps": rsStoreItems!ItemSize = "6.5": rsStoreItems!UnitPrice = 198#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "955183": rsStoreItems!Manufacturer = "AK Anne Klein": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Watches": rsStoreItems!ItemName = "Goldtone Mother-of-Pearl Dial Leather Watch": rsStoreItems!UnitPrice = 55#
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "204065": rsStoreItems!Manufacturer = "Kenneth Cole Reaction": rsStoreItems!Category = "Men": rsStoreItems!SubCategory = "Shirts": rsStoreItems!ItemName = "Dress Shirt, Urban Satin": rsStoreItems!ItemSize = "18 34/35": rsStoreItems!UnitPrice = 37.75
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "753222": rsStoreItems!Manufacturer = "Polo Ralph Lauren": rsStoreItems!Category = "Men": rsStoreItems!SubCategory = "Sweaters": rsStoreItems!ItemName = "Long-Sleeved Pima Cotton V-Neck Sweater": rsStoreItems!ItemSize = "S": rsStoreItems!UnitPrice = 95.95
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "388663": rsStoreItems!Manufacturer = "Lauren by Ralph Lauren": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Belts": rsStoreItems!ItemName = "Braided Leather Belt": rsStoreItems!ItemSize = "Medium": rsStoreItems!UnitPrice = 54.25
        rsStoreItems.Update
        rsStoreItems.AddNew
        rsStoreItems!ItemNumber = "211771": rsStoreItems!Manufacturer = "Ralph Lauren": rsStoreItems!Category = "Women": rsStoreItems!SubCategory = "Dresses": rsStoreItems!ItemName = "Petite Knit Tweed Short Sleeve Dress": rsStoreItems!ItemSize = "12": rsStoreItems!UnitPrice = 159.5
        rsStoreItems.Update
    
        rsEmployees.Close
        dbFunDS.Close
    End Sub
  11. Close Microsoft Visual Basic and return to Microsoft Access
  12. Switch the form to Form View and click the button
  13. In the Navigation Pane, right-click SellingItems and click Design View
  14. Change the following characteristics:
    Field Name Format Caption
    SellingItemID   Selling Item ID
    ItemNumber   Item #
    SubCategory   Sub-Category
    ItemName   Item Name
    ItemSize   Size
    UnitPrice Fixed Unit Price
  15. Save and close the table
  16. On the Ribbon, click File and click Options
  17. In the left list of the Access Options dialog box, click Current Database
  18. In the main list, click Overlapping Windows
  19. Click OK on the dialog box
  20. Click OK on the message box
  21. On the Ribbon, click File and click Close
  22. When asked whether you want to save changes to the form, click No
  23. In the list of files, click FunDS2
  24. On the Ribbon, click Create and, in the Forms section, click Form Design
  25. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Fun Department Store - New Shopping Session
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  26. Right-click the form and click Form Header/Footer
  27. Set the Back Color of the Form Header section to #C1B995
  28. Set the Back Color of the Detail section to #DDD9C3
  29. Set the Back Color of the Form Footer section to #7A4E2B
  30. Save the form as NewShoppingSession

Creating a List Box

To visually create a list box, display a form or report in the Design View. In the Controls section of the Ribbon, click the List Box button List Box and click the form or report. A wizard may come up to assist you.

To programmatically create a list box, call the CreateControl() method of the Application class. Specify the ControlType as acListBox. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acListBox)

    Set ctlGenders = Nothing
End Sub

The easiest way to configure a list box is when you are using the List Box Wizard to create it. The wizard allows you to select some columns of a table or query to display their values in the list box. If you select more than one column, the list box would display like a list view.

Practical LearningPractical Learning: Creating a List Box

  1. In the Controls section of the Ribbon, click the List Box button List Box
  2. Click the form
  3. In the first page of the wizard, make sure the first radio button is selected and click Next
  4. In the second page of the wizard, click Table: SellingItems

    List Box Wizard

  5. Click Next
  6. In the Available Items list, click the select all button Select All
  7. In the Selected Fields list, double-click SellingItemID

    List Box Wizard

  8. Click Next
  9. Click Next

    List Box Wizard

  10. Click Next
  11. Click Finish

Characteristics of, and Operations on, List Boxes

Introduction

A list box primarily uses the classic characteristics as a Windows control. For example, you can paint the body of a list box with a color your choice. If you apply a font and font characteristics (font color, font style, etc) to it, they would apply to all items of the list box.

When it comes to its functionality, the list box shares some characteristics with the combo box box. For example, the RowSourceType property is used to specify the type of list. The RowSource property specifies the list of items.

Practical Learning: Designing a List View

  1. Set the Back Color of the list box to #DDD9C3
  2. Save the form

The Columns of a List Box

Like a combo box, a list box can be made to appear like a list. You start by selecting various fields for the list box. By default, the list box doesn't show its column headers. To support this characteristics, (both) the list box (and the combo box) is (are) equipped with a Boolean property named Column Heads. If you want the list box to display the captions of a list box (or combo box), set this property to Yes or True.

By default, all columns of a list box (or combo box) display with the same width. To let you control the individual width of the colums, the controls is equipped with a property named Column Widths. Its value is a combination of numbers separated by semicolumns. The total widths of the column is represented by a property named Width.

Practical Learning: Formating a Date

  1. Make sure the list box is selected on the form.
    In the All tab of the Property Sheet, change the following characteristics:
    Column Widths: 0.75";1.15";0.95";1.15";3";1";1"
    Column Heads: Yes
    Width: 9
  2. Complete the design of the form as follows:

    Fun Department Store - New Shopping Session

    Control Name Caption Other Properties
    Label Label   Fun Department Store  
    Label Label   New Shopping Session  
    Line Line      
    Text Box Text Box txtItemNumber Item #:  
    Button Button cmdFindItem Find Item  
    Text Box Text Box txtReceiptNumber Receipt #:  
    Line Line      
    List Box List Box lbxSellingItems    
    Line Line      
    Text Box Text Box txtEmployeeNumber Employee #:  
    Text Box Text Box txtEmployeeName    
    Text Box Text Box txtOrderTotal Order Total:  
    Text Box Text Box txtShoppingDate Shopping Date/Time: Default Value: =Date()
    Text Box Text Box txtShoppingTime   Default Value: =Time()
    Text Box Text Box txtAmountTendered Amount Tendered:  
    Text Box Text Box txtSelectedItemNumber Selected Item #:  
    Button Button cmdRemoveFromShoppingList Remove From Shopping List  
    Text Box Text Box txtChange Change:  
    Button Button cmdSaveShoppingSession Save Shopping Session  
    Button Button cmdClose Close  
  3. On the form, right-click the Properties button of the form Properties and click Build Event...
  4. In the Choose Builder dialog box, click Code Builder and click OK
  5. Implement the event as follows:
    ' This procedure is used to get a receipt number from the Shopping Sessions table
    Private Sub GetReceiptNumber()
        Dim dbFunDS As Database
        Dim rsShoppingSessions As Recordset
        
        Set dbFunDS = CurrentDb
        ' Get a reference to the records of the ShoppingSessions table
        Set rsShoppingSessions = dbFunDS.OpenRecordset("ShoppingSessions", _
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
        
        ' If this is the first customer shopping, set the receipt number to 100001
        If rsShoppingSessions.RecordCount = 0 Then
            txtReceiptNumber = "100001"
        Else
            ' If this is not the first customer order, move to the last record
            rsShoppingSessions.MoveLast
            ' Get the (last) receipt number.
            ' Increase it by 1 and set it as the new receipt number.
            ' Display it in the appropriate text box
            txtReceiptNumber = CStr(CLng(rsShoppingSessions!ReceiptNumber) + 1)
        End If
        
        rsShoppingSessions.Close
        dbFunDS.Close
    End Sub
    
    Private Sub Form_Load()
        ' When the form opens, prepare the new receipt number
        GetReceiptNumber
    End Sub
  6. In the Object combo box, select txtEmployeeNumber
  7. In the Procedure combo box, select LostFocus and implement the event as follows:
    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbFunDS As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbFunDS = CurrentDb
        Set rsEmployees = dbFunDS.OpenRecordset("SELECT FirstName, LastName " & _
                                                "FROM Employees " & _
                                 "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';", _
                                                RecordsetTypeEnum.dbOpenForwardOnly, _
                                                RecordsetOptionEnum.dbAppendOnly, _
                                                LockTypeEnum.dbOptimistic)
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
        End If
        
        rsEmployees.Close
        dbFunDS.Close
    End Sub
  8. In the Object combo box, select cmdFindItem
  9. Implement the event as follows:
    Private Sub cmdFindItem_Click()
        Dim dbFunDS As Database
        Dim orderTotal As Double
        Dim rsSellingItems As Recordset
        Dim rsSelectedItems As Recordset
        
        ' When the user click the Find Item button, first make sure
        ' the Item # combo box contains a number. If there is no item number,
        ' let the user know and don't do anything more
        If IsNull(txtItemNumber) Then
            MsgBox "You must enter a valid and existing store item number.", _
            vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        orderTotal = 0#
        Set dbFunDS = CurrentDb
        
        ' To start, get the record from the StoreItems table if that record has the item number specified
        Set rsSelectedItems = dbFunDS.OpenRecordset("SELECT Manufacturer, Category, SubCategory, ItemName, ItemSize, UnitPrice " & _
                                                    "FROM StoreItems " & _
                                                    "WHERE ItemNumber = '" & txtItemNumber & "';", _
                                                    RecordsetTypeEnum.dbOpenForwardOnly, _
                                                    RecordsetOptionEnum.dbAppendOnly, _
                                                    LockTypeEnum.dbOptimistic)
        
        orderTotal = CDbl(Nz(txtOrderTotal))
        
        ' If there is a store item with the specified number, ...
        If rsSelectedItems.RecordCount > 0 Then
            Set rsSellingItems = dbFunDS.OpenRecordset("SellingItems", _
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
            ' ... copy that item number in the SellingItems table
            rsSellingItems.AddNew
            rsSellingItems!ItemNumber = txtItemNumber
            rsSellingItems!Manufacturer = rsSelectedItems!Manufacturer
            rsSellingItems!Category = rsSelectedItems!Category
            rsSellingItems!SubCategory = rsSelectedItems!SubCategory
            rsSellingItems!ItemName = rsSelectedItems!ItemName
            rsSellingItems!ItemSize = rsSelectedItems!ItemSize
            rsSellingItems!UnitPrice = rsSelectedItems!UnitPrice
            
            ' Add the new unit number to the current order total
            orderTotal = orderTotal + CDbl(Nz(rsSelectedItems!UnitPrice))
            rsSellingItems.Update
            
            ' Since the list box gets its values from the SellingItems table, update the list box
            lbxSellingItems.Requery
            
            ' Remove the previous item number in case the user(clerk/cashier)
            ' wants to select another store item
            txtItemNumber = ""
        End If
        
        ' Display the new order total in the appropriate text box
        txtOrderTotal = FormatNumber(orderTotal)
    End Sub
  10. In the Object combo box, select txtAmountTendered
  11. In the Procedure combo box, select LostFocus and implement the event as follows:
    ' This procedure is used to calculate the amount to hand to the customer
    ' based on the amount of the customer order and the amount the customers '
    ' gives to the cashier/clerk
    Private Sub txtAmountTendered_LostFocus()
        Dim orderTotal As Double
        Dim amountTendered As Double
        
        If IsNull(txtAmountTendered) Then
            Exit Sub
        End If
        
        orderTotal = CDbl(Nz(txtOrderTotal))
        amountTendered = CDbl(Nz(txtAmountTendered))
        
        txtAmountTendered = FormatNumber(amountTendered)
        txtChange = FormatNumber(amountTendered - orderTotal)
    End Sub
  12. In the Object combo box, select lbxSellingItems
  13. In the Procedure combo box, select Click
  14. Implement the event as follows:
    Private Sub lbxSellingItems_Click()
        ' If the user clicks an item in the list box to select it,
        ' put its item number in the Selected Item # text box
        txtSelectedItemNumber = lbxSellingItems
    End Sub
  15. In the Object combo box, select cmdRemoveFromShoppingList
  16. Implement the event as follows:
    ' This procedure is used if the user/cashier wants
    ' to remove an item from the customer order
    Private Sub cmdRemoveFromShoppingList_Click()
        Dim dbFunDS As Database
        Dim fldSellingItem As Field
        Dim rsSellingItems As Recordset
        
        ' If the user clicks the Remove button but its corresponding text
        ' box is empty, don't do nothing
        If IsNull(txtSelectedItemNumber) Then
            MsgBox "Make sure you first select an item from the list box", _
                   VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
                   "Fun Department Store"
            Exit Sub
        End If
        
        Set dbFunDS = CurrentDb
        ' Get the records of the SellingItems table
        Set rsSellingItems = dbFunDS.OpenRecordset("SellingItems", _
                                                   RecordsetTypeEnum.dbOpenTable, _
                                                   RecordsetOptionEnum.dbAppendOnly, _
                                                   LockTypeEnum.dbOptimistic)
        
        ' Start checking every record in the SellingItems table
        With rsSellingItems
            Do Until .EOF
                ' Check every column
                For Each fldSellingItem In .Fields
                    ' When you reach the Item # column...
                    If fldSellingItem.Name = "ItemNumber" Then
                        ' ... check its value to see if it corresponds to the selected item #
                        If fldSellingItem.Value = CLng(txtSelectedItemNumber) Then
                            ' If that's the case, remove that item from the shopping list
                            .Delete
                            
                            ' Let the user/cashier know
                            MsgBox "The item has been removed from the shopping list.", _
                                   VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
                                   "Fun Department Store"
                            Exit For
                        End If
                    End If
                Next
                .MoveNext
            Loop
        End With
            
        lbxSellingItems.Requery
        txtSelectedItemNumber = ""
        
        rsSellingItems.Close
        dbFunDS.Close
    End Sub
  17. In the Object combo box, select cmdSaveShoppingSession
  18. Implement the event as follows:
    Private Sub cmdSaveShoppingSession_Click()
        Dim dbFunDS As Database
        Dim fldSellingItem As Field
        Dim rsSoldItems As Recordset
        Dim rsSellingItems As Recordset
        Dim rsShoppingSessions As Recordset
        
        Set dbFunDS = CurrentDb
        Set rsSoldItems = dbFunDS.OpenRecordset("SoldItems", _
                                                RecordsetTypeEnum.dbOpenTable, _
                                                RecordsetOptionEnum.dbAppendOnly, _
                                                LockTypeEnum.dbOptimistic)
        Set rsSellingItems = dbFunDS.OpenRecordset("SellingItems", _
                                                    RecordsetTypeEnum.dbOpenTable, _
                                                    RecordsetOptionEnum.dbAppendOnly, _
                                                    LockTypeEnum.dbOptimistic)
        Set rsShoppingSessions = dbFunDS.OpenRecordset("ShoppingSessions", _
                                                      RecordsetTypeEnum.dbOpenTable, _
                                                      RecordsetOptionEnum.dbAppendOnly, _
                                                      LockTypeEnum.dbOptimistic)
        
        ' Make sure there is a receipt number in the appropriate text box
        If IsNull(txtReceiptNumber) Then
            MsgBox "You must provide a receipt number in order to save a shopping session.", _
                   VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
                   "Fun Department Store - New Shopping Session"
            Exit Sub
        End If
        
        If rsSellingItems.RecordCount < 1 Then
            MsgBox "In order to save a shopping session, the list box must contain at least one item.", _
                   VbMsgBoxStyle.vbOKOnly Or VbMsgBoxStyle.vbInformation, _
                   "Fun Department Store - New Shopping Session"
            Exit Sub
        Else
            rsShoppingSessions.AddNew
            rsShoppingSessions!ReceiptNumber = CLng(Nz(txtReceiptNumber))
            rsShoppingSessions!EmployeeNumber = txtEmployeeNumber
            rsShoppingSessions!ShoppingDate = txtShoppingDate
            rsShoppingSessions!ShoppingTime = txtShoppingTime
            rsShoppingSessions!OrderTotal = CDbl(Nz(txtOrderTotal))
            rsShoppingSessions!AmountTendered = CDbl(Nz(txtAmountTendered))
            rsShoppingSessions!Change = CDbl(Nz(txtChange))
            rsShoppingSessions.Update
            
            Do Until rsSellingItems.EOF
                rsSoldItems.AddNew
                For Each fldSellingItem In rsSellingItems.Fields
                    rsSoldItems!ReceiptNumber = CLng(Nz(txtReceiptNumber))
                    If fldSellingItem.Name = "ItemNumber" Then
                        rsSoldItems!ItemNumber = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "Manufacturer" Then
                        rsSoldItems!Manufacturer = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "Category" Then
                        rsSoldItems!Category = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "SubCategory" Then
                        rsSoldItems!SubCategory = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "ItemName" Then
                        rsSoldItems!ItemName = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "ItemSize" Then
                        rsSoldItems!ItemSize = fldSellingItem.Value
                    End If
                    If fldSellingItem.Name = "UnitPrice" Then
                        rsSoldItems!UnitPrice = fldSellingItem.Value
                    End If
                Next
                
                rsSoldItems.Update
                rsSellingItems.Delete
                rsSellingItems.MoveNext
            Loop
            
            GetReceiptNumber
            txtChange = "0.00"
            txtOrderTotal = "0.00"
            lbxSellingItems.Requery
            txtShoppingTime = Time()
            txtAmountTendered = "0.00"
        End If
        
        rsSellingItems.Close
        dbFunDS.Close
    End Sub
  19. In the Object combo box, select cmdClose
  20. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  21. Close Microsoft Visual Basic and return to Microsoft Access
  22. Close the form
  23. When asked whether you want to save, click Yes
  24. In the Navigation Pane, double-click NewShoppingSession

    Fun Department Store - New Shopping Session

  25. Click Employee #
  26. Type 402446 and press Tab
  27. In the Item # text box, type 211771 and click Find Item
  28. In the Item # text box, type 239679 and click Find Item
  29. In the Item # text box, type 290699 and click Find Item
  30. On the list box, click the record with the 239679 item number

    Fun Department Store - New Shopping Session

  31. Click the Remove From Shopping List button
  32. Click Amount Tendered
  33. Type 520 and press Tab

    Fun Department Store - New Shopping Session

  34. Click the Save Shopping Session button
  35. Click Employee # and type 927486
  36. In the Item # text box, type 955183 and click Find Item
  37. Click Amount Tendered, type 55 and press Tab
  38. Click the Save Shopping Session button
  39. In the Item # text box, type 345500 and click Find Item
  40. In the Item # text box, type 388729 and click Find Item
  41. In the Item # text box, type 239679 and click Find Item
  42. In the Item # text box, type 388663 and click Find Item
  43. Click Amount Tendered, type 400 and press Tab
  44. Click the Save Shopping Session button
  45. Click the Close button

Tree Views

Introduction

A tree view is a Windows control that appears like an upside down tree that displays a hierarchical list of items. A tree view starts in the top section with an object referred to as the root. The root can contain branches and leaves. In a computer application, a branch or a leaf is called a node or an item.

The items or nodes of a tree view have a type of relationship so that they are not completely independent. For example, a tree view can be based on a list that has a parent item and other child items that depend on that parent. In real world, if you cut a branch, the branches and leaves attached to it also disappear. This scenario is also valid for a tree view.

Creating a Tree View

In Microsoft Access, a tree view is provided as an ActiveX control. To get it, in the Controls section of the Ribbon, click the More button and click ActiveX Controls. Scroll down in the list box and select Microsoft TreeView Control:

ActiveX Controls - Microsoft Tree View Control

Click OK. This would add a representation of a tree view control to the form or report. You can then use and manage the control in either the Property Sheet or the Properties window. Normally, you should start by giving the control a meaningful name. This is equivalent to declaring a variable for it.

The tree view is available through a class named ThreeView. An item of a tree view is based on a class named Node. The branches (and/or leaves) of a tree view are stored in a property named Nodes. The Nodes property is a collection. As a collection, the Nodes property has the types of properties, methods, and behaviors we reviewed for the Collection class.

Introduction to Creating a Node

To let you create a new node, the Nodes collection is equipped with a method named Add. Its syntax is:

Public Function Add(ByVal Optional parent-node As String, _
		    ByVal optional relationship-placement As Variant, _
		    ByVal optional key As String, _
		    ByVal optional node-name As String, _
		    ByVal optional image-index As Integer, _
		    ByVal optional selected-image-index As Integer) As Node

All of the arguments of this method are optional. If you call the method without an argument, nothing would happen and you will not receive an error. Here is an example:

Private Sub cmdCreate_Click()
    tvExercise.Nodes.Add
End Sub

This would produce an empty tree view.

The Root Node of a Tree View

The first node of a tree view is called the root. You usually create it as the first node. To create the first node of a tree, pass only the fourth argument as the string to display. Here is an example:

Private Sub cmdCreate_Click()
    tvExercise.Nodes.Add , , , "College Park Auto-Parts"
End Sub

The Name of a Node

You will usually need to referr to a node in your code. For this reason, the Nodes.Add() method allows you to name a node. This is done by passing the third argument. The name can be anything you want. Here is an example:

Private Sub cmdCreate_Click()
    tvExercise.Nodes.Add , , "CPAP", "College Park Auto  Parts"
End Sub

A Reference to a Node

After creating a node, you may wnat to refer to its object. To make this possible, the Nodes.Add() method returns an object of type Node. To get a reference to a node, when creating it, call the method as a function and assign the call to a variable. Here is an example:

Private Sub cmdCreate_Click()
    Dim nodAutoPart As Node
    
    Set nodAutoPart = tvExercise.Nodes.Add(, , "CPAP", "College Park Auto  Parts")
    
    Rem Use the nodAutoPart variable
    
End Sub

A Node and its Children

Introduction

A node is referred to child if it is created below an existing node from which its existence depends. To create a child node:

  • Pass the first argument as the name of the node that will act as parent
  • Pass the third argument as a constant that represents a relationship between two nodes

To manage the relationship between a node you are creating and an existing, the tree view provides the following constants:

Constant Value Description
TvwFirst 0 This node will be created before all existing node
TvwLast 1 This node will be created after all existing node
TvwNext 2 This node will be created after the node named in the first argument
TvwPrevious 3 This node will be created before the node named in the first argument
TvwChild 4 This node will be created as a child of the node named in the first argument

Here is an example of creating a child node:

Private Sub cmdCreate_Click()
    tvExercise.Nodes.Add , , "CPAP", "College Park Auto-Parts"

    tvExercise.Nodes.Add "CPAP", tvwChild, , "Ford"
End Sub

The Number of Child Nodes

The number of nodes of a tree view are represented by the Count property of the Nodes collection.

Practical Learning: Ending the Lession

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2000-2016, FunctionX, Inc. Next