ACC2000: ApplyFilter Action in a Macro Is Not Applied as Expected After You Install SR-1 (269380)


The information in this article applies to:

  • Microsoft Access 2000 Service Release 1 (SR-1)
This article was previously published under Q269380
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you test your custom filter, you notice that the filter is not applied as expected. The filter works correctly the first time, but every time after that, the filter is ignored.

CAUSE

You have installed Microsoft Office 2000 Service Release 1 (SR-1), and you are applying the filter in one of the following ways:
  • You are using a Where Condition in the ApplyFilter action of a macro.
  • You are setting an SQL Where clause as the source of the filter.
  • You are applying a Filter method in Visual Basic for Applications without first setting the filter to "".
  • You are applying the DoCmd.ApplyFilter method in Visual Basic for Applications.

RESOLUTION

To resolve this behavior, apply the filter with the Filter method by setting the filter first to nothing ("") and then to the filter that you want. For an example of how to do this, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Open the Customers form in Design view, and then add a combo box to the form. Set the following properties for the combo box:
       Combo box
   ---------------------------------------------------------------------
   Name: FilterCombo
   RowSourceType: Table/Query
   RowSource: SELECT [Customers].[CustomerID], [Customers].[CompanyName]
              FROM [Customers]
   ColumnCount: 2
   ColumnWidths: 0";1"
  3. Set the AfterUpdate property of the combo box to the following event procedure:
    'Setting the filter to nothing and back
'to the original filter insures that
'it is not lost.

Forms![Customers].Filter = ""
Forms![Customers].FilterOn = False

Forms![Customers].Filter = "[CustomerID] = '" & _
  Forms![Customers]![FilterCombo] & "'"

Forms![Customers].FilterOn = True
  4. Save the form and open the form in Form view. Select several different items from the combo box, and note that the filter performs as expected.
NOTE: You can only resolve this behavior by using Visual Basic for Applications (VBA), as demonstrated in the previous example. You cannot resolve this issue by using a macro.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. On a computer that has Microsoft Office 2000 SR-1 installed, open the sample database Northwind.mdb.
  2. Create and save the following macro:
       Macro Name      Action
   -------------------------------
   FilterTest      ApplyFilter

   Action Arguments
   ---------------------------------------------------------------
   Where Condition: [CustomerID]=[Forms]![Customers]![FilterCombo]
  3. Open the Customers form in Design view, and then add a combo box to the form. Set the following properties for the combo box:
       Combo box
   --------------------------------------------------------------------
   Name: FilterCombo
   RowSourceType: Table/Query
   RowSource: SELECT [Customers].[CustomerID], [Customers].[CompanyName]
              FROM [Customers]
   ColumnCount: 2
   ColumnWidths: 0";1"
  4. Click the Event tab in the property sheet for the combo box, and then click the FilterTest macro in the list for the AfterUpdate property.
  5. Save the form, and then open the form in Form view. Select a customer from the combo box. Note that the correct record appears. Select another customer from the combo box, and note that the form does not update as expected.
Modification Type:MajorLast Reviewed:1/10/2001
Keywords:kbbug KB269380
©2002 Microsoft Corporation. All rights reserved.