Looks at the different types of item counts you can get for listboxes and combo boxes.
Access Archon #132
Introduction
Recently I received some emails from AW readers requesting help on counting selections in listboxes and combo boxes. This article deals with the different types of item counts you can get for listboxes and combo boxes. In the case of listboxes, the question was how to determine the number of items currently selected in a listbox, and for a combo box, the question was how to get a count of all the times a specific item had been selected in a combo box.
Listbox Counts
Access listboxes have a very useful collection, SelectedItems, that contains the items currently selected in a multiselect listbox. This collection has a Count property that gives the number of selected items. I made a sample form, frmListboxCount, with a multiselect listbox with a row source of the Northwind Categories table (renamed tblCategories). The form also has a command button whose Click event procedure calculates the number of items selected in the listbox, and an unbound textbox to display the value. The form is shown in Figure A.
Figure A. A listbox with a command button to show the number of items selected
VBA Codede
Private Sub cmdCountSelectedItems_Click()
On Error GoTo ErrorHandler
Dim lngCount As Long
Dim lst As Access.ListBox
Set lst = Me![lstSelect]
lngCount = Nz(lst.ItemsSelected.Count)
Me![txtCount].Value = lngCount
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Combo Box Count
Combo boxes have no ItemsSelected collection, since you can only select one item at a time in a combo box. However, you might want to record the number of times a particular item had been selected in a combo box, so I wrote code on the AfterUpdate event of a combo box on the frmComboBoxCount form to create new records in a table, tblCategorySelectionCount. I then made a totals query based on that table to give the totals by Category and Date, and display them in a datasheet subform. The form is shown in Figure B.
Figure B. A combo box with a subform displaying the number of times each item was selected
The row source of the combo box is a union query, with a blank item that shows at the top of the list (to avoid confusion when opening the form – if not for the blank selection, it would look as if the Beverages item was selected). The union query’s SQL statement is listed below:
SELECT tblCategories.CategoryID, tblCategories.CategoryName
FROM tblCategories
UNION SELECT 0 AS CategoryID, “” AS CategoryName
FROM tblCategories;
The Selection Count subform has as its record source the totals query qtotCategorySelectionCount, shown in design view in Figure C.
Figure C. A totals query that calculates the number of items selected by Category and Date. Click on the image to see the full size version.
VBA Code
The combo box’s AfterUpdate procedure is listed below:
Private Sub cboSelect_AfterUpdate()
On Error GoTo ErrorHandler
Dim lngCategory As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
lngCategory = Me![cboSelect].Value
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblCategorySelectionCount”)
With rst
.AddNew
![CategoryID] = lngCategory
![SelectionDate] = Date
![CountNumber] = 1
.Update
End With
Me![subCount].Requery
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
References
The code in the sample database needs the following reference (in addition to the default references):
Microsoft Scripting Runtime
If you import code or objects into a database of your own, you may need to set this reference. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of my Web site. It is accarch132.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Counting Selections.mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
