Doug tries to address commonly asked questions from Access developers. This month, he looks at various ways of using the List Box control including Multi-Select, moving items between lists, using non table record sources and showing all tables in a database in a list box .
I’ve got a list box that I’m using as a means of limiting what’s reported. It works fine when the list box doesn’t allow Multi Select. However, I’d like to be able to select more than one object at a time from the list box.
Unlike most of the other controls, referring to a multi select list box doesn’t return its value in any way that can be used in a query. Instead, you must use VBA code to determine which items have been selected.
In the Properties of the ListBox, you will find the Multi Select property in the Other Tab
To help in this, the List Box object has an ItemsSelected collection which provides a means to access data in the selected rows. The ItemsSelected collection is a collection of Variants, each one representing an integer index referring to a specific selected row in the list box. The collection has a single property associated with it, Count, which indicates how many items have been selected.
To list which items have been selected in a list box named lstItems, you can use code like the following:
Dim lngTotalSelected As Long Dim strMessage As String Dim varItem As Variant lngTotalSelected = _ Me.lstItems.ItemsSelected.Count If lngTotalSelected > 0 Then If lngTotalSelected = 1 Then strMessage = "You've selected the " & _ "following item:" & vbCrLf Else strMessage = "You've selected the " & _ "following " & lngTotalSelected & _ " items:" & vbCrLf End If For Each varItem In Me.lstItems.ItemsSelected strMessage = strMessage & _ Me.lstItems.Column(1, varItem) & vbCrLf Next varItem Else strMessage = "No items have been selected." End If MsgBox strMessage, vbOKOnly + vbInformation
It’s probably worth commenting on the use of Me.lstItems.Column(1, varItem) in the code above. This example assumes that the second column of the list box is the one that contains the important indicative information. This is because typically you’ll have the Id of each item as the (hidden) first column. Using Me.lstItems.Column(1, varItem) retrieves the contents of that second column for the particular row. If you wanted the bound column instead, you could use Me.lstItems.ItemData(varItem).
Now, when you use the OpenReport method to open your report, you have the ability to pass a Where clause to the report to limit what’s actually reported. Thus, to have the report limited to those items you’ve selected from your list box, you need to build a Where clause using essentially the same code as above.
In the accompanying database, I’ve copied several tables from the Northwinds database that comes with Access, as well as the Employee Sales By Country report (apologies for not creating my own report!). On the form that demonstrates how to use a multiselect list box as the basis for limiting what’s reported on the report, I have the following code in the Click event of a command button. Note that since I do want the value of Id field from the (hidden) first column, I’m using the ItemData property as I discussed above, but I could have just as easily used
Me.lstEmployees.Column(0, varItem): Private Sub cmdReport_Click() Dim varItem As Variant Dim strWhere As String strWhere = vbNullString
If at least one row has been selected in the list box, I loop through the ItemsSelected collection, creating a comma-separated list of the Ids corresponding to the selected rows.
If Me.lstEmployees.ItemsSelected.Count > 0 Then For Each varItem In _ Me.lstEmployees.ItemsSelected strWhere = strWhere & _ Me.lstEmployees.ItemData(varItem) & ", " Next varItem
Since I’m automatically appending a comma and space after each entry, the string is going to have an unnecessary comma and space at the end, so I use the Left function to trim those last two characters from the string. I then use the comma-delimited string I created with the IN operator to form the WHERE clause I’ll use when opening the report. strWhere would look something like [EmployeeId] IN (1, 3, 4). (Just in case you’re wondering, you can use an IN operator even if you only have a single value.)
strWhere = Left$(strWhere, Len(strWhere) - 2)
strWhere = "[EmployeeId] IN (" & strWhere & ")"
Now that I have the WHERE clause I want to use, I pass it as a parameter to the OpenReport method. I chose to use named parameters below: I could just as easily used
DoCmd.OpenReport "Employee Sales by Country", acViewPreview, , strWhere. DoCmd.OpenReport _ ReportName:="Employee Sales by Country", _ View:=acViewPreview, _ WhereCondition:=strWhere Else
There’s always a dilemma of what to do if nothing’s been selected in the list box. I decided to tell the user that he/she hasn’t selected anything, and give the option of opening the report with no limitation (i.e.: open the report showing all the employees):
If MsgBox("No employees selected." & _
vbCrLf & "Generate the report for " & _
"all employees?", vbYesNo + vbQuestion) _
= vbYes Then
DoCmd.OpenReport _
"Employee Sales by Country", _
acViewPreview
End If
End If
End Sub
If the field I wanted to use in the WHERE clause was text, rather than numeric, it would be necessary to put quotes around the values, so the line
strWhere = strWhere & Me.lstEmployees.ItemData(varItem) & ", "
would need to be
strWhere = strWhere & Chr$(39) & Me.lstEmployees.ItemData(varItem) & Chr$(39) & ", "
or
strWhere = strWhere & "'" & Me.lstEmployees.ItemData(varItem) & "', "
I’ve got two list boxes on my form. I’d like one to indicate those records that have been selected, and the other those records that have not been selected, and have the ability to move items from one list to the other. How can I do this?
In the accompanying sample database, I’ve modified the Products and Categories tables from the Northwinds database slightly to make it easier to illustrate how to do this. Rather than having a one-to-many relationship between Categories and Products (i.e.: each Product can belong in only one Category), I’ve introduced an intersection entity (which I named Catalog) which allows a Product to belong to more than one Category.
In the sample form that illustrates this technique, I’ve got a combo box named cboCategories that lists all of the possible Category values. Once the Category of interest is known, it’s possible to return a list of those Products which are linked with that Category using a query like:
SELECT Products.ProductID, Products.ProductName FROM Products INNER JOIN Catalog ON Products.ProductID = Catalog.ProductId WHERE Catalog.CategoryId = [Forms]![frmPairedListboxes]![cboCategories] ORDER BY Products.ProductName
Knowing which Products aren’t linked to that Category is a little trickier, but can still be done in SQL:
SELECT Products.ProductID, Products.ProductName FROM Products LEFT JOIN [SELECT ProductID, CategoryId FROM Catalog WHERE CategoryId= [Forms]![frmPairedListboxes]![cboCategories]]. AS Cat ON Products.ProductID = Cat.ProductId WHERE Cat.CategoryId Is Null ORDER BY Products.ProductName"
Those of you who are still using Access 97 may question the SQL above. It’s a little known fact that it is possible to include a SELECT statement rather than a table in a Join as I’ve done above in Access 97. The secret is to use square brackets around the subselect, and to put a period after the closing square bracket.
It turns out that getting the two list boxes populated is the hardest part of the exercise! In addition to the two list boxes, one showing those that have been selected, and one showing those that haven’t been selected, add two command buttons: one to transfer select additional products (i.e.: move from the Not Selected list to the Selected list), and one to remove selected products (i.e.: move from the Selected list to the Not Selected list).
Associating a Product with the selected Category involves inserting a new row into the Catalog table representing the Product/Category combination. Once the insertions have been completed, the data in the underlying tables has been changed, it’s necessary to refresh the two list boxes:
Private Sub cmdAddToList_Click()
Dim dbCurr As DAO.Database
Dim strSQL As String
Dim strWhere As String
Dim varItem As Variant
If Me.lstNotIn.ItemsSelected.Count > 0 Then
Set dbCurr = CurrentDb
For Each varItem In Me.lstNotIn.ItemsSelected
strSQL = "INSERT INTO Catalog (" & _
ProductId, CategoryId) " & _
"VALUES(" & _
Me.lstNotIn.ItemData(varItem) & ", " & _
Me.cboCategories & ")"
dbCurr.Execute strSQL
Next varItem
Me.lstIn.Requery
Me.lstNotIn.Requery
End If
End Sub
Disassociating a Product from the selected Category involves deleting the row representing the Product/Category combination from the Catalog table. Again, once the deletions have been completed, the data in the underlying tables has been changed, it’s necessary to refresh the two list boxes:
Private Sub cmdRemoveFromList_Click()
Dim dbCurr As DAO.Database
Dim strSelected As String
Dim strSQL As String
Dim strWhere As String
Dim varItem As Variant
If Me.lstIn.ItemsSelected.Count > 0 Then
For Each varItem In Me.lstIn.ItemsSelected
strSelected = strSelected & _
Me.lstIn.ItemData(varItem) & ", "
Next varItem
strSelected = _
Left(strSelected, Len(strSelected) - 2)
strWhere = "ProductId IN (" & _
strSelected & ")"
strSQL = "DELETE * FROM Catalog " & _
"WHERE CategoryId = " & _
Me.cboCategories & _
" AND (" & strWhere & ")"
Set dbCurr = CurrentDb
dbCurr.Execute strSQL
Me.lstIn.Requery
Me.lstNotIn.Requery
End If
End Sub
Sometimes it’s not convenient to have the data for a list box stored in a table. What other options are there?
Assuming you’re using Access 2002 or Access 2003, you can take advantage of the AddItem method of the list box, the same as VB programmers have been able to do for years.
Set the RowSourceType property of the list box (or combo box, for that matter) to “Value List”, and then you can use code like Me.MyListBox.AddItem “1;New Item” to add a new entry (with two columns) to the list box, or Me.MyListBox.RemoveItem(1) to remove the second item from the list box (remember that, unless you’ve got headers showing, the first row of the list box is row 0)
However, AddItem may not be the best approach. For one thing, it doesn’t work prior to Access 2002. While the accompanying database has an example of using AddItem (and RemoveItem), if you were to open the database using Access 2000, the sample form will fail (despite the fact that the database is in Access 2000 format).
Another way to load a list box (or combo box) is to write a custom function that will populate the control. Access will end up calling the function multiple times, passing different parameters each time, so it’s important that the function conform to what Access is expecting.
The Visual Basic function must accept five arguments. The first argument must be declared as a control and the remaining arguments as Variants. The function itself must return a Variant:
Function ListTableSizes( _ fld As Control, _ ID As Variant, _ row As Variant, _ col As Variant, _ code As Variant _ ) As Variant
Each time Access calls the function, it will provide values for the 5 parameters as follows:
The defined values for code are:
as the code parameter, it’s useful to create a framework like the following:
Select Case code Case acLBInitialize Case acLBOpen Case acLBGetRowCount Case acLBGetColumnCount Case acLBGetColumnWidth Case acLBGetFormat Case acLBGetValue Case acLBEnd End Select
The acLBInitialize section is the section in the code where whatever needs to be done to ensure that you’ve got the correct information available to populate the combo box or list box is carried out. The function returns True (or any Nonzero value) if the function can fill the list, or returns False (or Null) otherwise.
The acLBOpen section must return a nonzero ID value if the function can fill the list, or False (or Null) otherwise. Whatever value is returned by this section is what Access uses for all subsequent calls to the function for that particular control at that particular instance. In other words, if you’re using the same function to populate two different list boxes, you need to ensure that a different ID value is returned for the two controls so that Access can keep them straight. If you issue a Requery on the control, you can either use
the same value for ID, or simply ensure that a random number is issued. I typically use the value of the Timer function (which returns the number of seconds elapsed since midnight).
The acLBGetRowCount section returns the number of rows to be displayed. Remember that if the ColumnsHeads property is set to True, it’s necessary to return one more than the actual count.
The acLBGBetColumnCount section returns the number of columns to be displayed. This can’t be zero, and must match the property sheet value.
The acLBGetColumnWidth section returns the width (in twips) of the column specified by the col argument. Returning –1 indicates to use the default width.
The acLBGetFormat section returns a Format string to be used to format the list entry displayed in the row and column specified by the row and col arguments. Again, –1 indicates to use the default format.
The acLBGetValue section returns the value of the list entry to be displayed in the row and column specified by the row and col arguments passed to the function. If the ColumnsHead property is set to True, then row 0 is intended to return the column headers.
The acLBEnd section doesn’t actually return anything, but Access will always make a last call to the function passing this value. This allows you to have a section to do any cleanup activities that may be necessary.
Enough description, though. Let’s look at a sample function. One example Microsoft often presents lets you have a list box that contains 4 rows representing next Monday, followed by the next four Mondays:
Function ListMondays( _ fld As Control, _ id As Variant, _ Row As Variant, _ col As Variant, _ code As Variant) As Variant Dim intOffset As Integer Dim varRetVal As Variant Select Case code Case acLBInitialize varRetVal = True Case acLBOpen varRetVal = Timer Case acLBGetRowCount varRetVal = 4 Case acLBGetColumnCount varRetVal = 1 Case acLBGetColumnWidth varRetVal = -1 Case acLBGetValue intOffset = Abs((9 - Weekday(Date))Mod 7) varRetVal = Format(Date() + intOffset + _ 7 * row, "mmmm d") Case acLBGetFormat Case acLBEnd End Select ListMondays = varRetVal End Function
You can see that acLBInitialize returns True, as necessary, acLBOpen uses the Timer to return a random number, acLBGetRowCount returns that there are 4 rows, acLBGetColumnCount returns that there’s 1 column, acLBGetColumnWidth says to use the default width, acLBGetValue uses the row number to calculate different dates, and acLBGetFormat and acLBEnd return Null. (in actual fact, I could have left them out of the Select Case construct). Note that I’m passing a formatted value whenever acLBGetValue is passed as an argument. I could just have easily used acLBGetFormat to do that:
Case acLBGetValue intOffset = Abs((9 - Weekday(Date))Mod 7) varRetVal = Date() + intOffset + 7 * row Case acLBGetFormat varRetVal = "mmmm d"
To use this function, you set the list box’s RowSourceType property to the name of the function, and leave the RowSource property blank, as illustrated in Figure 1.
Figure 1: Configuring a list box to use a custom function to provide its values
List all of the non-system tables
The other sample function I have in the accompanying database lists all of the non-system tables in the database, as well as the number of rows in each. In this example, the acLBInitialize section actually does something. It creates a Static array that contains the names of the tables and their sizes.
Type TableDetails TableName As String TableRowCount As Long End Type Function ListTableSizes( _ fld As Control, _ id As Variant, _ Row As Variant, _ col As Variant, _ code As Variant _ ) As Variant On Error GoTo Err_ListTableSizes Static typTables() As TableDetails Static booHeaderShown As Boolean Dim dbCurr As DAO.Database Dim rsCurr As DAO.Recordset Dim tdfCurr As DAO.TableDef Dim lngLoop As Long Dim lngSize As Long Dim strDatabase As String Dim strSQL As String Dim varReturn As Variant Select Case code Case acLBInitialize
As mentioned above, I’m going to create a static array to contain the details for each of the non-system tables in the current database’s TableDefs collection. Since I don’t know in advance exactly how many nonsystem tables there are (TableDefs.Count returns system tables), I’ll initialize the array to the largest it could possibly be, and then resize it at the end once I know how many rows there actually are. This is because it’s relatively “expensive” to use the ReDim command.
Set dbCurr = CurrentDb() lngSize = dbCurr.TableDefs.Count ReDim typTables(lngSize) lngLoop = 0 For Each tdfCurr In dbCurr.TableDefs If (tdfCurr.Attributes And _ dbSystemObject) = 0 Then strSQL = "SELECT Count(*) As " & _ "TotalRows " & _ "FROM [" & tdfCurr.Name & "]" Set rsCurr = dbCurr.OpenRecordset( _ strSQL) If rsCurr.EOF Then lngSize = 0 Else lngSize = rsCurr!TotalRows End If rsCurr.Close typTables(lngLoop).TableName = _ tdfCurr.Name typTables(lngLoop).TableRowCount = _ lngSize lngLoop = lngLoop + 1 End If Next tdfCurr
Now that we’ve looped through all of the TableDef objects in the TableDefs collection, we know how many represent non-system tables. Assuming that at least 1 non-system table was found, redimension typTables to its proper size. If not, erase the array.
If lngLoop > 0 Then ReDim Preserve typTables(lngLoop - 1) Else Erase typTables End If Set dbCurr = Nothing varReturn = True
As illustrated before, the function will return Timer (to get a random number) when acLBOpen is passed as an argument:
Case acLBOpen varReturn = Timer
Remember that if the ColumnsHead property has been set to True for the list box, it’s necessary to return one more than the number of tables found when acLBGetRowCount is passed. (As explained earlier, the fld parameter is actually a reference to the list box, so it’s possible to check its ColumnsHead property, rather than having to hard-code it here):
Case acLBGetRowCount varReturn = UBound(typTables) - _ LBound(typTables) + _ IIf(fld.ColumnHeads, 2, 1)
Since it’s necessary that acLBGetColumnCount be correct, I retrieve the list box’s ColumnCount property. (Note that this assumes I’ve defined the list box correct on the form.)
Case acLBGetColumnCount varReturn = fld.ColumnCount I’ll simply use the default column widths. Case acLBGetColumnWidth varReturn = -1
I want the number of rows to be comma-separated, with no decimal point, so I set the format when the value passed for col is 1. (I’ll just use the default for col 0)
Case acLBGetFormat Select Case col Case 0 varReturn = -1 Case 1 If fld.ColumnHeads And _ Not booHeaderShown Then varReturn = -1 booHeaderShown = True Else varReturn = "#,##0" End If Case Else End Select
Finally! Here’s where the function returns a specific value, depending on what was passed for row and col. Remember that if the ColumnHeads property has been set, the first row (row = 0) needs to be the column headings. When that’s the case, the row count in the list box and the row in the array won’t correspond.
Case acLBGetValue If fld.ColumnHeads Then Select Case Row Case 0 Select Case col Case 0 varReturn = "Table Name" Case 1 varReturn = "Record Count" Case Else End Select Case Else Select Case col Case 0 varReturn = _ typTables(Row - 1).TableName Case 1 varReturn = _ typTables(Row - 1).TableRowCount Case Else End Select End Select Else Select Case col Case 0 varReturn = _ typTables(Row).TableName Case 1 varReturn = _ typTables(Row).TableRowCount Case Else End Select End If
That’s the function pretty much completed, other than doing whatever cleanup’s required when acLBEnd is passed.
Case acLBEnd Erase typTables End Select End_ListTableSizes: ListTableSizes = varReturn Exit Function
The error trapping is relevant. Since the array is erased if there’s nothing in it, the call to the function when code is acLBGetRowCount can cause an error 9. In that case, I want to return 1 if the ColumnHeads property is set, or 0 otherwise:
Err_ListTableSizes: Select Case Err.Number Case 9 ' This will occur when the array isn't initialized varReturn = IIf(fld.ColumnHeads, 1, 0) Case Else Err.Raise Err.Number, "ListTableSizes", Err.Description End Select Resume End_ListTableSizes End Function
You can see that acLBInitialize returns True, as necessary, acLBOpen uses the Timer to return a random number, acLBGetRowCount returns the number of rows (4), acLBGetColumnCount returns the column count (1), acLBGetColumnWidth says to use the default width, acLBGetValue uses the row number to calculate different dates, and acLBGetFormat and acLBEnd return Null.
In this example, I’m passing a formatted value whenever acLBGetValue is passed as an argument. I could have just as easily used acLBGetFormat to return a format string to be used with my data:
Case acLBGetValue intOffset = Abs((9 - Weekday(Date))Mod 7) varRetVal = Date() + intOffset + 7 * row Case acLBGetFormat varRetVal = "mmmm d"
To use this function, you just need to set the ListBox’s RowSourceType property to the name of the function, and leave the RowSource property blank, as shown in Figure 1.
With that simple function out of the way, I’ve included a more interesting function in the sample database. My function lists all of the non-system tables in the database, as well as the number of rows in each.
