Microsoft KB Archive/135546: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 54: | Line 54: | ||
This article shows you how to use a multiple-selection list box to restrict records in a recordset. The two methods described in this article 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.<br /> | This article shows you how to use a multiple-selection list box to restrict records in a recordset. The two methods described in this article 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.<br /> | ||
<br /> | <br /> | ||
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 | 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. | ||
</div> | </div> | ||
Line 77: | Line 77: | ||
Row Source: Customers | Row Source: Customers | ||
Column Count: 2 | Column Count: 2 | ||
Column Widths: .5 | Column Widths: .5";2" | ||
Multi Select: Extended | Multi Select: Extended | ||
Width: 2.5 | Width: 2.5" | ||
</pre></li> | </pre></li> | ||
<li>Add a command button to the form. Set the Name property to Command2, and set the OnClick property to [Event Procedure].</li> | <li>Add a command button to the form. Set the Name property to Command2, and set the OnClick property to [Event Procedure].</li> | ||
Line 92: | Line 92: | ||
' Build criteria string from selected items in list box. | ' Build criteria string from selected items in list box. | ||
Criteria = | Criteria = "" | ||
For Each i In Me![List0].ItemsSelected | For Each i In Me![List0].ItemsSelected | ||
If Criteria <> | If Criteria <> "" Then | ||
Criteria = Criteria & | Criteria = Criteria & " OR " | ||
End If | End If | ||
Criteria = Criteria & | Criteria = Criteria & "[CustomerId]='" _ | ||
& | & Me![List0].ItemData(i) & "'" | ||
Next i | Next i | ||
Line 124: | Line 124: | ||
RowSource: Customers | RowSource: Customers | ||
ColumnCount: 2 | ColumnCount: 2 | ||
ColumnWidths: .5 | ColumnWidths: .5";2" | ||
Multiselect: Extended | Multiselect: Extended | ||
Width: 2.5 | Width: 2.5" | ||
</pre></li> | </pre></li> | ||
<li><p>Add a command button to the form. Set the Name property to Command4 and set the OnClick property to the following event procedure:<br /> | <li><p>Add a command button to the form. Set the Name property to Command4 and set the OnClick property to the following event procedure:<br /> | ||
Line 143: | Line 143: | ||
For Each Itm In ctl.ItemsSelected | For Each Itm In ctl.ItemsSelected | ||
If Len(Criteria) = 0 Then | If Len(Criteria) = 0 Then | ||
Criteria = Chr(34) & | Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) | ||
Else | Else | ||
Criteria = Criteria & | Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _ | ||
& | & Chr(34) | ||
End If | End If | ||
Next Itm | Next Itm | ||
If Len(Criteria) = 0 Then | If Len(Criteria) = 0 Then | ||
Itm = MsgBox( | Itm = MsgBox("You must select one or more items in the" & _ | ||
" list box!", 0, "No Selection Made") | |||
Exit Sub | Exit Sub | ||
End If | End If | ||
Line 158: | Line 158: | ||
' Modify the Query. | ' Modify the Query. | ||
Set DB = CurrentDb() | Set DB = CurrentDb() | ||
Set Q = DB.QueryDefs( | Set Q = DB.QueryDefs("MultiSelect Criteria Example") | ||
Q.SQL = | Q.SQL = "Select * From Orders Where [CustomerID] In(" & Criteria & _ | ||
");" | |||
Q.Close | Q.Close | ||
' Run the query. | ' Run the query. | ||
DoCmd.OpenQuery | DoCmd.OpenQuery "MultiSelect Criteria Example" | ||
End Sub | End Sub | ||
Line 178: | Line 178: | ||
== REFERENCES == | == REFERENCES == | ||
For more information about the MultiSelect property of a list box, search for | For more information about the MultiSelect property of a list box, search for "MultiSelect property" using the Microsoft Access 97 Help Index.<br /> | ||
<br /> | <br /> | ||
For more information about the ItemData property of a list box, search for | For more information about the ItemData property of a list box, search for " ItemData property" using the Microsoft Access 97 Help Index.<br /> | ||
<br /> | <br /> | ||
For more information about the Column property of a list box, search for | For more information about the Column property of a list box, search for "Column property" using the Microsoft Access 97 Help Index. | ||
</div> | </div> |
Latest revision as of 12:26, 21 July 2020
Article ID: 135546
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q135546
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to use a multiple-selection list box to restrict records in a recordset. The two methods described in this article 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:
- Open the sample database Northwind.mdb.
- Create a new, blank form based on the Customers table and open it in Design view.
- 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.
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"
- 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
- Open the form in Form view.
- Select multiple items in the list box: click the first item, then hold down the CTRL key, and click subsequent items.
- 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:
- Open the sample database Northwind.mdb.
- Create a new query based on the Orders table and include all the fields. Save the query as MultiSelect Criteria Example.
- Create a new, blank form based on the Customers table and open it in Design view.
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"
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
- Open the form in Form view.
- Select multiple items in the list box by holding down the CTRL key and clicking the different items.
- 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: listbox inf
Keywords: kbhowto kbusage KB135546