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

MORE INFORMATION

Method 1

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].
6. 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
   					

7. Open the form in Form view.
8. Select multiple items in the list box: click the first item, then hold down the CTRL key, and click subsequent items.
9. 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

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
      Width: 2.5"
   					

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.