Meyyammai Subramanian
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Access 2002

Summary: This article explains how to add "group filter control" functionality to two or more drop-down list boxes, and Find dialog box functionality to a data access page. (6 printed pages)

Contents

Add "Group Filter Control" Functionality to Two or More Drop-Down List Boxes
Add a Find Button to a Data Access Page

Add "Group Filter Control" Functionality to Two or More Drop-Down List Boxes

You use a group filter control to retrieve records that contain the value you selected in the control. However, a data access page supports only one group filter control for each group level. If you want to filter records based on the values you select from multiple list boxes, you can do so programmatically.

The following illustration shows a data access page with two drop-down list boxes that act like group filter controls. The page does not display data until you select a value in each of the list boxes.

Figure 1. A data access page with two filter controls

For example, if you select Davolio, Nancy from the first list and Brazil from the second list, the page will retrieve all orders handled by Nancy that are being shipped to Brazil.

Figure 2. A data access page with two filter controls

Add Multiple Filter Controls to a Page

1. Create a data access page with the fields and group levels you want.
2. Make sure you include the filter fields as drop-down list boxes in the header section. 

   In the above page, the list boxes are named cboEmployee and cboShipCountry.

3. Set the ListRowSource, ListBoundField, and ListDisplayField properties for each of the list boxes.

   For example, for the ShipCountry list box, set ListRowSource to Recordset: Orders, ListBoundField to ShipCountry, and ListDisplayField to ShipCountry.

4. Click the arrow in the header section bar of the outermost group level, and then click Caption.
5. Clear the ControlSource property of the filter list boxes, and then move the list boxes to the caption section.
6. To prevent the outermost group level from retrieving records before the selection is made, in the Data Outline window, set the ServerFilter property of the outermost RecordsetDef object to an expression that returns False.

   For example, select the Orders RecordsetDef entry in the data outline, and then type 1=0 in the ServerFilter property box.

7. Remove the record navigation section for the group level that includes the filter controls.
8. In the DataPageComplete event, add code to clear the selected text in the list boxes and to hide all other sections in the page.

   For more information on how to add code to controls on a page, see Microsoft Access Help.

   The following is a sample DataPageComplete event procedure.

   <SCRIPT language=vbscript event=DataPageComplete(dscei) for=MSODSC>
   <!--
   
   ' fInited is used to make sure this code runs only the first time the 
   ' page loads.
   If (fInited = FALSE And dscei.DataPage.GroupLevel.RecordSource = _
                                                          "Orders") Then
       fInited = TRUE
       ' Make the list boxes initially empty.
       cboEmployee.value = ""
       cboShipCountry.value = ""
       ' Use Undo as a workaround to hide the initial band that displays 
       ' when the page loads.
   
       MSODSC.Datapages(0).Undo
   End If
   -->
   </SCRIPT>
   

9. In the OnChange event of the list boxes, call the OnFilterComboChange subprocedure that filters the recordset based on the values you select in the list boxes.

   For example, the OnChange event for the cboEmployee list box looks like this:

   <SCRIPT language=vbscript event=OnChange for=cboEmployee>
   <!--
       OnFilterComboChange()
   -->
   </SCRIPT>
   

10. In a separate global script block, initialize the variable that ensures that the code in the DataPageComplete event runs just once, and then define the OnFilterComboChange subprocedure.

    The following is a sample script block that initializes the fInited variable and defines the OnFilterComboChange subprocedure.

    <SCRIPT language=vbscript>
    Dim fInited
    fInited=FALSE
    Sub OnFilterComboChange()
    Dim stWhere
    
    ' Don't do anything unless user selects a value from each list box.
    If (cboEmployee.value <> "" And cboShipCountry.value <> "") Then
        stWhere = "EmployeeID=" & cboEmployee.value & " "
        stWhere = stWhere & "AND ShipCountry='" &cboShipCountry.value& "' "
        ' Set the server filter on the Orders recordset.
        MSODSC.RecordsetDefs.Item("Orders").ServerFilter = stWhere
    End If
    End Sub
    

Add a Find Button to a Data Access Page

When a page is open in Page view, you can filter for records that match the selected values by using the Filter by Selection button in the record navigation toolbar. However, the toolbar doesn't include a Find button that enables you to search the recordset for specific records. You can create your own Find button by adding a command button to the page and attaching an event procedure to the button's OnClick event.

The following illustration shows the Products data access page, which has a Find a Product button.

Figure 3. A data access page with a Find button

When you click the Find a Product button, the page displays the Find dialog box. The dialog box prompts you to enter the ID of the product record you want to display. When you click OK, the corresponding record is displayed if it is found in the recordset. Otherwise, the page displays an error message.

Add a Find Button to a Page

1. Open a data access page in Design view.
2. Make sure the Control Wizards tool in the toolbox is not selected. This turns off the wizard.
3. In the toolbox, click the Command Button tool.
4. On the data access page, click the command button in the header section that corresponds to the recordset that you want to search.
5. Set the properties of the command button to make it look the way you want.
6. In the OnClick event of the Find button, add code to clone the recordset, to prompt and accept input from the user, and to search the cloned recordset for the corresponding record.

   For more information on how to add code to controls on a page, see Microsoft Access Help.

   The following is a sample OnClick event procedure for the Find button.

   <SCRIPT language=vbscript event=onclick for=btnFind>
   <!--
   ' Clone the recordset.
   Dim rs
   Set rs = MSODSC.DataPages(0).Recordset.Clone
   
   On error resume next
   ' This line assumes that the value you are filtering on is an integer.
   ' If the search value is a string, use slightly different syntax.
   ' For example, "CustomerID = '" & CStr(InputBox("Please enter _
                                  customer to find", "Find")) & "'" 
   rs.find "ProductID=" & cLng(inputbox("Enter a ProductID","Find"))
   
   ' Custom error handling.
   If (err.number <> 0) Then
       Msgbox "Error: " & err.number & " " & err.description,, _
                                                     "Invalid Search"
       Exit Sub
   End If
   
   ' Check search results for success.
   If (rs.bof) or (rs.eof) Then
       Msgbox "No Product found",,"Search Done"
       Exit Sub
   End If
   
   MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
   -->
   </SCRIPT>
   

   Note   When you create Visual Basic® Scripting Edition (VBScript) blocks for Microsoft Office Data Source Control (MSODSC) events, you must add a parameter to the event name, as follows:

   <SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>
   

   The oEventInfo parameter is used to return specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script won't work without it.