Access97 to Access 2000 Macros

[Judy]

I have converted a database from 97 to 2000. In 97 a
Macro with an ApplyFilter runs the Filter afresh each
time allowing the user to input the search.
In 2000 this runs fine the first time but when the
relevant button is clicked on subsequent occasions it
merely re-applies the previous selection.
This also occurs with a newly written Macro on the same
lines.
I have tried adding Refresh and RemoveFilterSort in
advance of the ApplyFilter to no avail. The ClearGrid
option says it is not applicable.
Help please. I have said I will convert this database for
someone else and they are waiting for it.

 

[Amy Vargo]

Hi Judy,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.

This is a known issue. 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.

This can occur if 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.

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.

For more information, please see the article below:

269380 ACC2000: ApplyFilter Action in a Macro Is Not Applied as Expected
After
http://support.microsoft.com/?id=269380

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.