There are several things that annoy me about the default behavior of DoCmd.OpenReport. To address these annoyances, I built a replacement function that I named PreviewReport().
In this series of articles, I will take you through the evolution of this function as I address each of my frustrations.
Part 4: Cascading Maximize
If you open a report from a windowed form, maximize the report, then close it, the windowed form from which you opened the report gets maximized. This is easier to explain with some screenshots:
I find this behavior annoying because:
- I always use windowed forms (not the tabbed interface)
- My forms are best viewed in the "Restored" state, not "Maximized"
- Users generally want to see the report preview in full screen
- Users find the behavior jarring because the form they were just viewing is not the way they left it
- They call me to report the "bug"
I've solved this problem in a variety of ways over the years.
My first approach was to add a DoCmd.Maximize to every report Open event and a DoCmd.Restore to every report Close event.
This was no good because sometimes the users opened the report from a form that was maximized. Then when they close the report, their previously maximized form is now restored to a smaller size.
Additionally, it meant that I had to add the above code to the Open and Close events of every report. At first, I just entered
=DoCmd.Maximize into the Report's On Open event and
=DoCmd.Restore into the Report's On Close event within the property sheet. It was relatively easy to automate the process by looping through each report in code, opening it in Design View, assigning the properties, then saving and closing the report. However, this all blew up in my face when calls to the DoCmd method were treated as "unsafe expressions," and thus verboten within the report and form Property Sheet Event tab.
Storing window state
The next evolution in my approach was to save the state of the form window from which I opened the report. I would still call DoCmd.Maximize in every report Open event. However, in the close event, I would first check a global variable to see if the calling form window had a state of Maximized or Restored. If the form was previously Restored, then I would call DoCmd.Restore. Otherwise, I would do nothing when the report closed.
This was an improvement for the users, but it was even more cumbersome to maintain for me as a developer. Plus it relied on global state, something to avoid if possible.
Maximizing without DoCmd.Maximize
What I eventually settled on was to maximize the report without actually calling DoCmd.Maximize. How did I do that? By using a few Windows API calls to resize the report to occupy all of the available space within the Access canvas (the canvas is the gray area under the ribbon and above the status bar that occupies the bulk of the Access window).
To do this, I used my FillAccessWindow() function.
This fulfilled the user's goal of seeing as much of the report as possible on screen, but without all of the side effects of using the DoCmd.Maximize method.
I also check to see if the calling form is already maximized. If it is, then I don't need to use the FillAccessWindow() function because the report will open in Maximized mode, thus filling the available space.
Here's what this approach looks like:
In the Part 3 article, I had to split the function into two different versions: one that works with vbWatchdog and one that works without. Rather than do that here, I'll just show the code needed to implement this particular workaround. In my final article of the series, I will present two complete working versions of the function.
Here are the interesting bits for this workaround:
Dim IsMaxed As Boolean, ActiveHwnd As Long ActiveHwnd = ActiveObjectHwnd If ActiveHwnd <> 0 Then IsMaxed = IsMaximized(ActiveHwnd) Else IsMaxed = False End If '...open the report... If Not IsMaxed Then FillAccessWindow Reports(RptName).Hwnd 'We call it a second time because the first call may ' add scroll bars/status bars that need to be accounted for DoEvents FillAccessWindow Reports(RptName).Hwnd End If
The code above requires this additional function, which I'll write about in more detail in a future article:
Function ActiveObjectHwnd() As Long On Error Resume Next ActiveObjectHwnd = Screen.ActiveForm.hWnd If ActiveObjectHwnd <> 0 Then Exit Function ActiveObjectHwnd = Screen.ActiveReport.hWnd If ActiveObjectHwnd <> 0 Then Exit Function ActiveObjectHwnd = Screen.ActiveDatasheet.hWnd End Function
The code also references these additional functions:
Auto-Print: Opening a report sends it directly to the printer with no on-screen preview Wrong Data: If you call OpenReport on a report that's already open, Access doesn't apply the new criteria to the report Unwarranted Errors: When you cancel report opening in the OnNoData event, Access raises an error Cascading Maximize: If you open a report from a windowed form, maximize the report, then close it, the windowed form from which you opened the report gets maximized
- Missing OpenArgs: Prior to Access 2007, OpenArgs was not supported for the OpenReport method
UPDATED 3/16/2021: Added links to prerequisite functions.