Apply a Filter to an Access 2000 form Using Combo Boxes (Article)

Category:
Office/VBA
Type:
Snippets
Difficulty:
Intermediate

Author:ahmed

Version Compatibility: Visual Basic 6, ASP

Instructions:

David Nishimoto davepamn@reli.net 

 

Overview: This article explains how to apply a filter to an Access 2000 form by using a combo box as a filter parameter. You can use the filter property to specify a subset of recordset to be displayed.

Create the People Table 1. PeopleId, AutoNumber 2. PeopleAffected, Text Create the Process Table 1. ProcessId, AutoNumber 2. ProcessName, Text Create the ProcessToPeople Table 1. Id, Autonumber 2. PeopleId, Number 3. ProcessId, Number Create a Query call qryPeopleAffected 1. Join the Process table to the ProcessToPeople table on the ProcessId field. 2. Join the People table to the ProcessToPeople table on the PeopleId field

Create a Form called PeopleAffected

 

1. Use the form wizard
2. Select the qryPeopleAffect query as the date source
4. Select Columnar as the type of form
5. Title the form, "People Affected"
6. In the Form Header drag and drop an unbound combo box change the caption to "filter."
7. Open the Properties dialog for the combo box. Name the combobox "cboSelectableProcess"
8. Start the Query Builder (QB) from the "Record Source" item property.
9. QB: Select the Processes table and drag and drop the ProcessId and ProcessName fields into the design grid. Click X to close the window and answer "yes" to updating the property with the sql. Change column count property item to 2. This will allow you to see the processid and processname in the combo box.
10. Launch the Code builder from "On Click" property item for the combo box. Choose the code builder option.
11. Insert the following code fragment

 

Private Sub cboSelectableProcess_Click() Dim sProcessId With cboSelectableProcess If .ListIndex <> -1 Then sProcessId = .SelText Filter = "ProcessId=" & sProcessId FilterOn = True MsgBox "Filter Applied" End If End With End Sub

12. Dataview: The final result is all records displaying during initial load, and as the user selects a processid from the cboSelectableProcess combo box, the code parameterizes the filter's "Where" clause with the selected process id.