ACC: Cannot Completely Remove Form Filter

Last reviewed: August 1, 1997
Article ID: Q172284
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you apply a filter or sort to a form and then click Remove Filter/Sort on the Records menu, it appears that the filter or sort has been removed. However, if you check the Filter property and OrderBy property in a Visual Basic for Applications procedure or in the form's property sheet, you see that these properties contain the values that were in effect before you removed the filter or sort.

CAUSE

The Apply Filter/Sort and Remove Filter/Sort commands on the Records menu change only the values of the FilterOn and OrderByOn properties. These commands do not affect the values of the forms's Filter or OrderBy properties. Selecting Remove Filter/Sort on the Records menu simply sets FilterOn and OrderbyOn to False. As a result, the form displays all of the records, because, although the filter or sort is still present, it is no longer in effect.

RESOLUTION

There are two methods for completely removing a filter or sort from a form.

Method 1

Use the form's property sheet to remove the filter or sort. To do so, follow these steps:

  1. Open the form in Design view.

  2. On the Edit menu, click Select Form.

  3. On the View menu, click Properties and select the Data tab.

  4. Delete the contents of the Filter box or the OrderBy box.

Method 2

Use a macro or a Visual Basic for Applications procedure to remove a filter or sort. In the following example, the filter and sort are removed during the Current event of the form.

   Private Sub Form_Current()
      If Me.FilterOn = False Then
         Me.Filter = ""
         Me.OrderBy = ""
      End If
   End Sub

Note that if you use this event procedure to remove a filter, the only way to activate the filter again is to recreate it. On the Records menu, point to Filter and click the appropriate command to recreate the filter.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access and open the sample database Northwind.mdb.

  2. Open the Customers form in Design view, and add a command button to the form. Set its properties as follows:

          Name: btnShowFilter
          Caption: Show Filter
    

  3. Set the OnClick property of the command button to the following event procedure:

          Private Sub btnShowFilter_Click()
    

             MsgBox Me.Filter
    
          End Sub
    
    

  4. On the View menu, click Form View.

  5. Select the contents of the Country field, which should be "Germany."

  6. On the Records menu, point to Filter and click Filter by Selection. Note that only the records of customers located in Germany are displayed. The text to the right of the navigation buttons also indicates that the records have been filtered.

  7. Click Show Filter and note that the value of the Filter property is

          ((Customers.Country = "Germany"))
    

  8. On the Records menu, click Remove Filter/Sort. Note that the records of all customers are now displayed.

  9. Click Show Filter and note that the value of the Filter property is still as follows:

          ((Customers.Country = "Germany"))
    

REFERENCES

For more information, search the Help Index for "Filter property," "FilterOn property," "OrderBy property," and "OrderByOn property."

Keywords          : FmsProp kbusage
Version           : 7.0,97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 1, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.