Filtering and Sorting Data in Forms and Reports

One of the most important functions of a database application is to make it easy for users to find the data they need to use or change. For example, in the Orders sample application, users need a way to find existing orders easily. Microsoft Access provides powerful tools for users to find, filter, and sort records. Depending on your users' needs, you can allow them to use existing tools, or provide your own ways to accomplish these tasks.

This section describes the following approaches to filtering and sorting data in your application:

Using Standard Filter and Sort Commands If you make the Filter By Selection, Filter By Form, and Sort commands available on your application's forms, users can easily filter and sort records themselves. If you want, you can customize these features by responding to events.

Opening a Form or Report with a Filter You can use a number of methods to open forms or reports in your application so that they show a subset of records.

Changing the Filter or Sort Order of a Form or Report After a form or report is open, you can change the filter or sort order in code, or apply or remove the filter.

If you have special needs for finding and filtering data in your application, you may want to provide your own filtering interface. In this case, you'll use a combination of the previous techniques, creating your own forms where users can specify the records they want to see. For example, you may know that there are only a few fields the user wants to filter on. By displaying your own form, you can provide a straightforward interface for filtering on important fields while ignoring others.

Using Standard Filter and Sort Commands

Unless you set properties or change menus and toolbars to make them unavailable, the following filter and sort commands are available on the Records menu and the toolbar in Form view:

Filter By Selection

Filter By Form

Advanced Filter/Sort

Apply Filter/Sort

Remove Filter/Sort

Sort Ascending

Sort Descending

Using these commands, users of your application can easily filter and sort records themselves. When a user applies a filter or sort order using these commands, Microsoft Access sets the Filter, FilterOn, OrderBy, and OrderByOn properties for the form accordingly, and requeries records on the form.

Note When a user changes the filter or sort order of a form and then closes the form, Microsoft Access saves this information. The last sort order saved is reapplied automatically the next time the form is opened, and the user can reapply the last filter saved by clicking the Apply Filter button .

See Also For more information on the Filter By Selection, Filter By Form, or Advanced Filter/Sort commands, search the Help index for the name of the command.

Disabling Filter and Sort Features

In some cases, you don't want users of your application to filter records. To disable the standard filtering commands on a form, set the AllowFilters property to No.

If you want to disable sorting, or if you want to disable some filtering features while allowing others, create custom menu bars and toolbars for your form that include the commands and buttons you want and leave off the ones you don't want to make available.

See Also For more information on custom menu bars and toolbars, see Chapter 1, "Creating an Application."

Responding to Filter Events

If you want to change the way that standard filtering and sorting commands work, you can write event procedures for the Filter and ApplyFilter events. For example, you may want to display a message each time a user uses the Filter By Form or the Advanced Filter/Sort command for a form to remind the user to specify criteria and then apply the filter.

If you want to display a message or take other action when a user uses the Filter By Form or Advanced Filter/Sort command, write an event procedure for the Filter event. To help you respond to each command, the Filter event procedure has a FilterType argument that tells you which of these commands was selected.

If you want to cancel the filtering command the user chose, you can set the Cancel argument for the event procedure to True.

The following event procedure compares the FilterType argument to the acFilterByForm and acFilterAdvanced constants to display a different message to the user depending on which filtering command was chosen.

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
   Dim strMsg As String
   Select Case FilterType
      Case acFilterByForm
         strMsg = "Specify the records you want to see by choosing "
         strMsg = strMsg & "from the lists, then click Apply Filter."
      Case acFilterAdvanced
         strMsg = "Drag fields to the filter design grid, specify "
         strMsg = strMsg & "criteria and sort order, then click "
         strMsg = strMsg & "Apply Filter."
   End Select
   MsgBox strMsg               ' Display the message.
End Sub

If you want to display a message or take other action when a user applies or changes a filter, write an event procedure for the ApplyFilter event. This event occurs whenever a user chooses the Apply Filter/Sort, Remove Filter/Sort, or Filter By Selection command, and whenever the user closes the Filter window without applying the filter. The ApplyFilter event procedure has an ApplyType argument that tells you which of these actions was taken so you can respond in different ways.

If you want to cancel the filtering command the user chose, you can set the Cancel argument for the event procedure to True, and the filter won't be applied.

The following event procedure displays a message if the user is applying a filter. The message shows the setting of the Filter property and gives the user a chance to cancel the operation.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
   Dim strMsg As String, intResponse As Integer
   If ApplyType = acApplyFilter Then
      strMsg = "You've chosen to filter for the following criteria:"
      strMsg = strMsg & vbCrLf & Me.Filter
   End If
   ' Display the message box and get an OK or Cancel response.
   intResponse = MsgBox(strMsg, vbOkCancel + vbQuestion)
   If intResponse = vbCancel Then Cancel = True
End Sub

See Also For more information on arguments for the Filter events, search the Help index for "Filter event" and "ApplyFilter event." For more information on events, see Chapter 6, "Responding to Events."

Customizing the Standard Filtering Interface

When a Northwind sales representative starts the Orders application, the Orders form opens for data entry (no existing records are available). To find an existing order in the database, the employee clicks the Filter Orders button on the Orders toolbar.

You want the filtering interface on the Orders form to be as straightforward as possible. Because most fields aren't appropriate for setting criteria, you don't want the employee to worry about them. To make them less obtrusive, you can disable all the fields except those you expect a user to set criteria for.

The Filter event procedure for the Orders form disables all but three fields (BillTo, EmployeeID, and OrderDate).

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
' If Filter By Form, disable all but three fields.
   If FilterType = acFilterByForm Then
      BillTo.SetFocus
   Dim ctl As Control
   For Each ctl In Me.Controls
      Select Case ctl.ControlType
         Case acTextBox, acComboBox, acOptionGroup, acSubForm
            Select Case ctl.Name
               Case "BillTo", "EmployeeID", "OrderDate"
            Case Else
               ctl.Enabled = False
            End Select
      End Select
   Next ctl
   DoCmd.ShowToolbar "Orders Form Toolbar", acToolbarNo
   End If
End Sub

If you set properties in the Filter event procedure, be sure to reset them in the ApplyFilter event procedure.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Reset fields after filtering.
   Dim ctl As Control
   For Each ctl In Me.Controls
      Select Case ctl.ControlType
         Case acTextBox, acComboBox, acOptionGroup, acSubForm
            Select Case ctl.Name
               Case "OrderID", "Subtotal", "Total"
            Case Else
               ctl.Enabled = True
            End Select
      End Select
   Next ctl
   BillTo.SetFocus
   DoCmd.ShowToolbar "Orders Form Toolbar", acToolbarYes
End Sub

Controlling Combo Boxes in Filter By Form

You can control whether text boxes on a form display suggested values in drop-down combo boxes in the Filter By Form window. For example, if you have a large set of records or if your data is stored on a network, you may want to prevent Microsoft Access from running queries to fill all the lists. By default, Filter By Form displays lists of values for indexed and non-indexed fields if the record source for the form is in the current database or a linked table, but doesn't display them if the record source is a linked Open Database Connectivity (ODBC) database. To change this behavior, set the FilterLookup property for the text box in form Design view, or set Filter By Form options on the Edit/Find tab of the Options dialog box (Tools menu).

See Also For more information on Filter By Form, search the Help index for "Filter By Form."

Opening a Form or Report with a Filter

When you use Visual Basic code or a macro to open a form or report, you may want to specify which records to display. This is especially useful for reports, because users can't directly filter records in a report.

When your application provides a customized way for users to open a form or print a report, you can specify the records to display in the form or report in several ways. A common approach is to display a custom dialog box where the user enters criteria for the form or report's underlying query. To get the criteria, you refer to the controls in the dialog box. The following sections describe three ways you can use criteria entered in a custom dialog box to filter records.

Using the wherecondition Argument

The wherecondition argument of the OpenForm or OpenReport method or action is the simplest way to get criteria in situations where a user is providing only one value. For example, the PrintInvoiceDialog form in the Orders sample application prompts users to select an OrderID for the invoice they want to print. If you're using an event procedure, you can apply a filter that displays only one record by adding an argument to the OpenReport method, as shown in the following line of code:

DoCmd.OpenReport "Invoice", acViewPreview, , "OrderID = " & OrderID

The "OrderID = " in the filter expression refers to the OrderID field in the Invoice report's underlying query. The OrderID on the right side of the expression refers to the value the user selected from the OrderID list box in the dialog box. The expression concatenates the two, causing the report to include only the invoice for the record the user selected.

See Also For more information on the PrintInvoiceDialog form, see the example "Creating a Dialog Box to Print Invoices" earlier in this chapter.

Note If you're using a macro, you can use the following Where Condition argument in the OpenReport action that prints the report.

OrderID = [Forms]![PrintInvoiceDialog]![OrderID]

The wherecondition argument is applied only by the event procedure or macro specified for the OnClick event of the button that runs the OpenForm or OpenReport method or action. This gives you the flexibility of using any number of different dialog boxes to open the same form or report and applying different sets of criteria depending on what the user wants to do. For example, the user may want to print an invoice for a certain customer or view orders only for a certain product. If users open the form or report in the Database window rather than through your dialog box, however, no criteria are applied to the query and all its records are displayed or printed. To prevent this, you can hide the Database window by clearing the Display Database Window check box in the Startup dialog box (Tools menu).

See Also For more information on Startup options, see "Setting Startup Options" in Chapter 1, "Creating an Application."

You can use the wherecondition argument to set criteria for more than one field, but if you do, the argument setting quickly becomes long and complicated. In those situations, specifying criteria in a query may be easier.

Using a Query as a Filter

A separate query, sometimes called a filter query, can refer to the controls on your dialog box to get its criteria. Using this approach, you filter the records in a form or report by setting the filtername argument of the OpenForm or OpenReport method or action to the name of the filter query you create. The filter query must include all the tables in the record source of the form or report you're opening. Additionally, the filter query must either include all the fields in the form or report you're opening, or you must set its OutputAllFields property to Yes.

For example, to create a filter query for the Invoice report, make a copy of the report's underlying query and save it under another name. Then, add criteria to the OrderID field in the filter query that refers to the control on the dialog box. (If the filter query's OutputAllFields property is set to Yes, this is the only field you need to include in the filter query as long as you include all the tables that contain fields on the report.)

After you create and save the query you'll use as a filter, set the filtername argument of the OpenReport method or action to the name of the filter query. The filtername argument applies the specified filter query each time the OpenReport method or action runs.

Using a query as a filter to set the criteria has advantages similar to using the wherecondition argument of the OpenForm or OpenReport method or action. A filter query gives you the same flexibility of using more than one dialog box to open the same form or report and applying different sets of criteria depending on what a user wants to do. If users open the form or report in the Database window rather than through your dialog box, however, no criteria are applied to the query and all its records are displayed or printed. To prevent this, you can hide the Database window by clearing the Display Database Window check box in the Startup dialog box (Tools menu).

See Also For more information on Startup options, see "Setting Startup Options" in Chapter 1, "Creating an Application."

Directly Referring to Dialog Box Controls in a Form or Report's Underlying Query

You can also refer to the dialog box controls directly in the form or report's underlying query instead of through the arguments of the OpenForm or OpenReport method or action. For example, instead of referring to the control on the PrintInvoiceDialog form in a filter query's criteria as shown in the previous illustration, you can set the exact same criteria in the Invoice report's underlying query, Invoices.

Using this approach, the OpenForm or OpenReport method or action requires no wherecondition or filtername argument. Instead, each time you open a form or report, its underlying query looks for the dialog box to get its criteria. However, if a user opens the form or report in the Database window rather than through your dialog box, Microsoft Access displays a parameter box prompting the user for the dialog box value. To prevent this, you can hide the Database window by clearing the Display Database Window check box in the Startup dialog box (Tools menu).

See Also For more information on Startup options, see "Setting Startup Options" in Chapter 1, "Creating an Application."

Changing the Filter or Sort Order of a Form or Report

After a form or report is open, you can change the filter or sort order in response to users' actions by setting form and report properties in Visual Basic code or in macros. For example, you may want to provide a menu command or a toolbar button that users can use to change the records that are displayed. Or you may have an option group control on a form that users can use to select from common sorting options.

To set the filter of a form or report, set its Filter property to the appropriate wherecondition argument, and then set the FilterOn property to True. To set the sort order, set the OrderBy property to the field or fields you want to sort on, and then set the OrderByOn property to True. If a filter or sort order is already applied on a form, you can change it simply by setting the Filter or OrderBy properties.

When you apply or change the filter or sort order by setting these properties, Microsoft Access automatically requeries the records in the form or report. For example, the following code changes the sort order of a form based on a user's selection in an option group:

Private Sub SortOptionGrp_AfterUpdate()
   Const conName = 0, conDate = 1
   Select Case SortOptionGrp
      Case conName
         Me.OrderBy = "LastName, FirstName"   ' Sort by two fields.
      Case conDate
         Me.OrderBy = "HireDate DESC"         ' Sort by descending date.
   End Select
   Me.OrderByOn = True                        ' Apply the sort order.
End Sub

Whether the filter and sort order get set in code or by the user, you can apply or remove them by setting the FilterOn and OrderByOn properties to True or False. For example, you could add a button to a report's custom toolbar that runs the following macro to apply or remove a filter you specified when opening the report.

Synchronizing Records by Changing the Filter

The Orders sample application controls which record appears in the ProductsPopup form by setting the form's Filter property. You can keep the ProductsPopup form synchronized with the Orders form; this will make sure the ProductsPopup form always shows details about the current product when you move from record to record in the Orders subform.

To do this, write an event procedure for the subform's Current event that sets the pop-up form's Filter property. (You can also do this by writing a macro you specify as the subform's OnCurrent event property setting.) The following code example shows the event procedure for the Current event of the Orders subform. This event procedure uses the IsLoaded function from the UtilityFunctions module that is included with the Orders sample application.

Private Sub Form_Current()
   .
   .
   .
   If IsLoaded("ProductsPopup") Then
      ' If there's no current product record, display a blank pop-up window,
      ' otherwise filter to show the current product.
      If IsNull(ProductID) Then
         strFilter = "ProductID = 0"
      Else
         Forms!ProductsPopup.Filter = "ProductID = " & ProductID
      End If
      Forms!Orders.SetFocus               ' Set focus back to Orders subform.
      Forms!Orders!OrdersSubform.SetFocus
   End If
   .
   .
   .
End Sub

Each time a user changes records in the subform, the procedure resets the Filter property of the pop-up form, causing it to display the corresponding record.

Casa de Bender