List-Based Windows Controls: The List Box
|
|
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.:
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:
| 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:
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:
| 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:
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.
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
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| Field Name | Format | Caption |
| SellingItemID | Selling Item ID | |
| ItemNumber | Item # | |
| SubCategory | Sub-Category | |
| ItemName | Item Name | |
| ItemSize | Size | |
| UnitPrice | Fixed | Unit Price |
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
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
| 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 | |
and click Build Event...' 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 SubPrivate 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 SubPrivate 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' 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 SubPrivate 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' 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 SubPrivate 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 SubPrivate Sub cmdClose_Click()
DoCmd.Close
End Sub
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:
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
|
|
||
| Previous | Copyright © 2000-2022, FunctionX, Inc. | Next |
|
|
||