Access Answers: Let me check my list…

<< Click to Display Table of Contents >>

Navigation:  Access Controls >

Access Answers: Let me check my list…

Doug Steele       2006-

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 .

200603_DS_Demo

 

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.

 

200603_DS_Demo1

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?

 

200603_DS_Demo2

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?

200603_DS_Demo5

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:

200603_DS_tbl1

The defined values for code are:

 

200603_DS_tbl2

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.

 

200603_DS1
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.

200603_DS_Demo4

 

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.

 

 

download Your download is called   Steele_LetMeCheckMyList.accdb  and you can download it on this page   Access Controls >>

 

 

 

Other Pages On This Site You Might Like To Read

Using List Regions with Many-to-Many Relationships

Tricks With Combo Boxes

Drilling with Combo Boxes

Analyze Your Data in Space

Creating Paired Listbox Controls, Part 1

Use Classes to Enhance List and Combo Boxes