MS Access Report Horizontal Scroll Bar Bug

A new bug appeared when previewing reports starting with Access 2013 or 2016. When certain reports opened, the horizontal scroll bar would get drawn on the screen and get stuck in its original draw location.  Certain actions, such as using the navigation buttons, would cause the scroll bar to get redrawn directly on top of the navigation buttons, completely obscuring them.

This error seemed to appear randomly.  It only seemed to affect a handful of reports in a handful of applications.  For a long time, I assumed the error was truly random and, hence, not reproducible.

But, today, I finally managed to isolate and reliably reproduce the bug.  Here are the steps to reproduce it on affected reports (not all reports are affected):

1. Open the report in Print Preview
2. When the report is resized, the horizontal scroll bar remains on-screen in its original position
3. Changing pages redraws the horizontal scroll bar on top of the navigation buttons, completely obscuring them

Now you see it, now you don't

This bug has bothered me for years.  There were a few simple workarounds for it, so I never pursued a permanent fix for it.  Additionally, I thought this bug affected every report in newer versions of Access.

Today, I realized that not every report was behaving this way.  What's more, I was able to reliably reproduce the bug with some reports, but not with others.

Avoid DoCmd.ApplyFilter

The culprit, it turns out, is the DoCmd.ApplyFilter method.  The affected reports were calling this method in the Report_Open() event handler.  

In the 13 years I've been writing Access applications, I don't think I've ever used DoCmd.ApplyFilter; there are better ways to achieve that functionality than relying on the DoCmd object.  However, I do maintain many Access applications that are over 20 years old.  This technique is relatively common in those applications.  I think this approach to filtering may be a holdover from the Access 95 or Access 2.0 days.

In the reports I've worked on since I discovered the source of the bug, I've been able to rewrite the code to pass the filtering criteria in the Where argument of the DoCmd.OpenReport method.  This completely replaces the need to call DoCmd.ApplyFilter in the Report_Open routine.

Other culprits?

I'm sure I've seen this error occur in other reports that don't rely on the DoCmd.ApplyFilter method.  Unfortunately, I don't know what those other reports are right now, so I have no way of isolating the offending behavior.  At least now I know that this behavior is report-specific, and it can be traced back to code-behind in the Report_Open method.  

My guess is that other culprits beyond DoCmd.ApplyFilter are lurking in the Report_Open method, too.  If you know of any–or if you have seen this bug in the wild–I would love to hear about it.  You can reach me on Twitter (@nolongerset) or via email (mike at nolongerset.com).