Access add items to listbox
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.:
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 SubIntroduction 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 ListItemAll 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 SubThis 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 SubThis 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 SubIt 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 SubThe 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 SubThe 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 ColumnHeaderThe 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 SubThis 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 SubThe 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 SubThe 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 SubThis 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 SubThis 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 ClearThe 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 ListItemAll 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 SubThis 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 SubThis 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 SubThis 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
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 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 SubThe 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
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
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
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 NodeAll 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 SubThis 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 SubThe 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 SubA 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 SubA 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:
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 SubThe 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 |