Access Answers: Let me check my list…

<< Click to Display Table of Contents >>

Navigation:  Combo Boxes and List Boxes >

Access Answers: Let me check my list…

Doug Steele      

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).

ad5468x60

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.

 

Your download file is called 603STEELE.ZIP in the file SA2006-03down.zip

This can be purchased with all the other downloads on this page

 

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

SQL Discrepancies, List Boxes, and Still Trying to Get a Date