ACC: How to Use a Multi-Select List Box to Filter a Form

ID: Q135546

The information in this article applies to:

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you two methods for how to use a multiple-selection list box to restrict records in a recordset. These methods are more flexible than other techniques, such as applying a query or an SQL WHERE clause because they enable you to choose at random which records are displayed.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

Both of the following methods work by restricting the records in a recordset based on the items that you select in a multiple-selection list box.

Method 1

This method uses the Filter property of a form. To create this method, follow these steps:

1. Open the sample database Northwind.mdb.

2. Create a new, blank form based on the Customers table and open it in 

   Design view.
3. If the field list is not displayed, on the View menu, click Field List 
   and drag the CustomerID and CompanyName fields from the field list to
   the detail section of the form.
4. Add an unbound list box to the form's detail section and set its 
   properties as follows:

      Name: List0
      Row Source: Customers
      Column Count: 2
      Column Widths: .5";2"
      Multi Select: Extended
      Width: 2.5"
5. Add a command button to the form. Set the Name property to Command2, and 
   set the OnClick property to [Event Procedure]. On the View menu, click
   Code, and type the following text in the Form module:

      Option Compare Database
      Option Explicit

      Private Sub Command2_Click()
         Dim Criteria As String
         Dim i As Variant

         ' Build criteria string from selected items in list box.
         Criteria = ""
         For Each i In Me![List0].ItemsSelected
            If Criteria <> "" Then
                  Criteria = Criteria & " OR "
            End If
            Criteria = Criteria & "[CustomerId]='" _
               & Me![List0].ItemData(i) & "'"
         Next i

         ' Filter the form using selected items in the list box.
         Me.Filter = Criteria
         Me.FilterOn = True

      End Sub
6. Open the form in Form view.

7. Select multiple items in the list box: click the first item, then 

   hold down the CTRL key, and click subsequent items.
8. Click the command button. Note that the form's recordset is restricted 
   to the records that you selected in the list box. If you clear the
   items and click the button again, you remove the filter and restore all
   the records.

Method 2

This method modifies the QueryDef object of a query. To create this method, follow these steps:

1. Open the sample database Northwind.mdb.

2. Create a new query based on the Orders table and include all the fields. 

   Save the query as MultiSelect Criteria Example.
3. Create a new, blank form based on the Customers table and open it in 
   Design view.
4. Add an unbound list box to the form's detail section and set its 
   properties as follows:

      Name: List0
      RowSource: Customers
      ColumnCount: 2
      ColumnWidths: .5";2"
      Multiselect: Extended
5. Add a command button to the form. Set the Name property to Command4 and 
   set the OnClick property to the following event procedure:

   NOTE: This example uses the ItemData property to return values from
   the Bound Column of the list box. To return a value from a column other
   than the Bound Column use the Column property instead of the ItemData
   property.

      Private Sub Command4_Click()
      Dim Q As QueryDef, DB As DATABASE
      Dim Criteria As String
      Dim ctl As Control
      Dim Itm As Variant

      ' Build a list of the selections.
      Set ctl = Me![List0]

      For Each Itm In ctl.ItemsSelected
         If Len(Criteria) = 0 Then
            Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
         Else
            Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
            & Chr(34)
           End If
         Next Itm

         If Len(Criteria) = 0 Then
            Itm = MsgBox("You must select one or more items in the_
            list box!", 0,"No Selection Made")
         Exit Sub
         End If

      ' Modify the Query.
      Set DB = CurrentDb()
      Set Q = DB.QueryDefs("MultiSelect Criteria Example")
      Q.SQL = "Select * From Orders Where [CustomerID] In(" & Criteria &_
         ");"
      Q.Close

      ' Run the query.
      DoCmd.OpenQuery "MultiSelect Criteria Example"

      End Sub
6. Open the form in Form view.

7. Select multiple items in the list box by holding down the CTRL key and 

   clicking the different items.
8. Click the command button. Note that the query is restricted to the 
   records that you selected in the list box.

REFERENCES

For more information about the MultiSelect property of a list box, search for "MultiSelect property" using the Microsoft Access 97 Help Index.

For more information about the ItemData property of a list box, search for " ItemData property" using the Microsoft Access 97 Help Index.

For more information about the Column property of a list box, search for "Column property" using the Microsoft Access 97 Help Index.

Additional query words: 

Keywords          : kbusage FmsCmbo FmsHowto kbfaq
Version           : WINDOWS:7.0,97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: February 12, 1999