Access add items to listbox

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

Topic 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 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 Learning: Creating a List Box

1. In the Controls section of the Ribbon, click the List Box button
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

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

8. Click Next
9. Click Next

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:

   Control	Name	Caption	Other Properties
   Label		Fun Department Store
   Label		New Shopping Session
   Line
   Text Box		txtItemNumber	Item #:
   Button		cmdFindItem	Find Item
   Text Box		txtReceiptNumber	Receipt #:
   Line
   List Box		lbxSellingItems
   Line
   Text Box		txtEmployeeNumber	Employee #:
   Text Box		txtEmployeeName
   Text Box		txtOrderTotal	Order Total:
   Text Box		txtShoppingDate	Shopping Date/Time:	Default Value: =Date[]
   Text Box		txtShoppingTime	Default Value: =Time[]
   Text Box		txtAmountTendered	Amount Tendered:
   Text Box		txtSelectedItemNumber	Selected Item #:
   Button		cmdRemoveFromShoppingList	Remove From Shopping List
   Text Box		txtChange	Change:
   Button		cmdSaveShoppingSession	Save Shopping Session
   Button		cmdClose	Close

3. On the form, right-click the Properties button of the formand 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

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

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

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:

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:

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