Flaws in Microsoft Access

Provided by allenbrowne.com, January 2004. Updated May 2006

Incorrect filtering

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.

1. FilterOn not set/removed correctly for form and subform combination

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:

1. Open the BadFilter form. The main form shows "1 of 8", and the subform "1 of 12".
2. Click the Filter Main Form command button. The main form nav buttons now read, "1 of 2 (Filtered)".
3. Click the Filter subform button. The subforms nav buttons now read "1 of 4 (filtered)".
4. Click the Remove subform filter button. The subform reads "1 of 12" which is correct. The main form reads "1 of 2", i.e. the word "(Filtered)" has gone. Clicking the Remove Main form Filter button now fails to restore all 12 records to the main form, because Access falsely believes the main form filter is already off.

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:

1. Open the BadFilter form.
2. Click Filter Main Form. Results in "1 of 2 (Filtered)."
3. Click Filter subform. Results in "1 of 4 (Filtered)".
4. Click the Remove Main form filter button. The main form shows all 8 records (correct), but the subform filter is also removed. The subform filter should be an independent property.

2. OrderBy property of main form interferes with FilterOn of subform

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:

1. Open the BadFilter2 form. The main form shows "1 or 8", and the subform "1 of 77".
2. Click the Filter subform command button. It now shows "1 of 4 (Filtered)".
3. Click the Sort main form button. The subform shows all 77 records again, i.e. its Filter has been lost.

3. FilterOn is not set reliably for reports

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:

1. Open the Immediate window (Ctrl+G).
2. Enter this command:
       DoCmd.OpenReport "FilterOnProblem", acViewPreview, , "TheDate = #1/1/2003#"
3. In the Immediate window, ask Access if a filter is applied:
       ? Reports!FilterOnProblem.FilterOn

You will find that Access has set the report's Filter property to match the WhereCondition, but the FilterOn property has not been set.

4. Filter applied to wrong report (multiple instances)

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.

Conclusion

Do not discard filters. They are still very useful. Just be aware of the flaws and avoid those cases.