Provided by Allen Browne, January 2004. Last updated: May 2006
Update: The bulk of these bugs have been fixed in Access 2007.
Each form has its own Filter property. However, Access gets confused if you apply a filter to both a form and its subform. It also fails to maintain the FilterOn property for reports.
These flaws occur in all versions of Access that have filters (Acc. 95 onwards). Demonstrations of the first three of these bugs are found in AccessFlaws.zip.
If a form and its subform are both filtered and you remove the filter from the subform, Access no longer recognises that the main form is filtered. The word "(Filtered)" disappears from the form's navigation buttons. It does not show all the records, but attempting to turn off the main form's filter fails, and falsely reports its FilterOn property is False.
To demonstrate this bug:
If a form and its subform are both filtered and you remove the filter from the main form, the filter from the subform is also removed. The subform filter should be maintained independently.
To demonstrate this bug:
If a subform is not using the LinkMasterFields/LinkChildFields properties, setting the OrderBy property of the main form removes any filter applied to the subform
To demonstrate this bug:
If you open a form with a WhereCondition, Access reports the form's FilterOn is True. If you open a report with a WhereCondition, Access may not set its FilterOn property. Any code in the report's events is therefore unable to determine whether if the Filter is applied or not.
To demonstrate this bug:
You will find that Access has set the report's Filter property to match the WhereCondition, but the FilterOn property has not been set.
You can open multiple instances of a report with the New keyword, e.g.
Set rpt = New Report_MyReport
There is no WhereCondition with this approach, so you need to set the report's Filter in its Open event. Unfortunately, Access may fail to set the filter, or to apply it to the wrong instance.
DoEvents can help Access to get the right instance, but can you trust the results? Will it still work on future processors? on future versions? on hardware you cannot test with because it does not exist yet? In effect, the flaw means you cannot trust filters with multiple report instances.
The best workaround is to reassign the RecordSource of the report instance in its Open event instead of using a filter.
Do not discard filters. They are still very useful. Just be aware of the flaws and avoid those cases.
Home | Index of tips | Top |