ApplyFilter Problem

[keiem]

I have a database that was designed in Access 2000. However, my firm recently upgraded to Access 2007, and I'm having difficulty with one of the macros I created that had worked in the older version.

In the 2000 version of the database, I have a button on a form that runs a macro on the 'on-click' event containing the following commands:

1. ShowAllRecords
2. RunCommand: RemoveFilterSort
3. ApplyFilter: qryFindAPN

What this is supposed to do, is to clear any existing filter and run a query named qryFindAPN. qryFindAPN queries a single field and has a criteria setting that prompts the user to enter data - in this case an Assessors Parcel Number (APN). The intent is that the user can use this button as many times as they need to, to query records without closing and reopening the form. The on-click event and macro work just fine in Access 2000/2003.

In Access 2007, however, the macro will run once successfully. Thereafter, it simply reapplies the previous filter - even after clearing to show all records - and does not give the user a prompt for data input.

I searched the forums on my own to attempt to find a solution to this problem. All references point to the use of the following code to solve the problem:

Me.FilterOn = False
Me.Filter = ""

Going with this, I replaced the prior macro in the on-click event with the following:

Me.FilterOn = False
Me.Filter = ""
DoCmd.ApplyFilter "qryFindAPN"

Unfortunately, this script is producing the same results as the macro. It will run successfully the first time after the form has been opened - prompting the user for an APN - but thereafter will just reapply the prior filter, even thought the FilterOn and Filter="" statements appear to be running correctly.

Could someone tell me what it is I'm doing wrong here?


Thanks,

Chris

 

[Prattaratt]

1. Try executing the query directly, outside the form, and see if it runs properly that way. This will narrow the problem to either the query itself, or the control that the filter data is taken from. If it turns out to be the query itself, I would try removing the parameter and re-entering it. During an upgrade, sometimes things don't always transfer over properly. If it turns out to be the control itself, I would try hard coding the filter sql statement into the filter property like so:

dim SQL as String
(...)
rem edAPN is the editor control on the form where you set the APN
rem APN is the APN field in the recordsource
  s= "[APN] = " &edAPN.Value
  FilterOn = false
  filter = SQL
  FilterOn = true
(...)

let us know if that works

 

[keiem]

When you say "try hard coding the filter sql statement in the filter property" do you mean the 'On Filter' event property of the form, or somewhere else? I took you to mean the 'On Click' event property, and inserted it there. Unfortunately, it comes to the same result. The filter will work once, on the first time. There after, it simply reapplies the previous filter, instead of prompting for a new APN.

Also, I check the query. It works fine.

 

[drbtodd1971]

I have a database that was written in Access 2000 and was working fine. We have now had a rollout of Access 2003 and the database works but the filter button returns no records. When running it from 2000 it returns 18 records.
The only workround I can find is to put 2000 back on that machine but that is only a short term solution.

Has anyone got any ideas or know of any bugs with filters in 2003?