[AccessD] Report Preview Auto Fit to Window (solved)

Hi all

It should be noted that this trick and code is obsolete for A2002/XP and beyond as in these versions you just set the AutoResize property to True. Although the on-line help talks about a "complete record", I've found that the report is resized to display a complete page:

---
The AutoResize property uses the following settings.

Setting   Visual Basic     Description 
Yes     True (Default)    The Report window is automatically sized to display a complete record. 
No      False                   When opened, the Report window has the last saved size.
---

Thus my code should only be used for A2000 (which John uses) and below.

/gustav

>>> Gustav at cactus.dk 27-06-2007 17:24 >>>
Hi John et al

I modified the modules slightly. Now the helper form remains open (but still hidden) but "disabled" (timer interval is now set to zero) while the report is open.
When the report closes, it closes the form too. Now you can print the preview as usual - before this action consistently would crash Access when printing initialized.

The work principle is identical: When the report opens, it opens the helper form hidden. The timer event of the helper form runs a function that sets the report to Fit-to-window.
The difference is that before the helper form was closed by its own timer event; now it remains open and but inactive and is closed by the closing event of the report.

Report module:

<code>

Option Compare Database
Option Explicit

  ' Name of timer form which will resize this report.
  Const cstrFormName  As String = "frmReportZoom"

Private Sub Report_Close()

  ' Close the timer form.
  DoCmd.Close acForm, cstrFormName, acSaveNo

End Sub

Private Sub Report_Page()

  ' Specify requested zoom level (percent).
  ' Useful values are from 10 to 200 percent.
  ' Specify zero if report shall fit to window.
  Const clngZoomLevel As Long = 0
  
  Dim lngZoomLevel    As Long
  Dim strOpenArgs     As String

  ' Adjust zoom level at first page view only.
  If Me.Page = 1 Then
    ' Wrap zoom level into a valid constant.
    Select Case clngZoomLevel
      Case Is <= 0
        lngZoomLevel = acCmdFitToWindow
      Case Is <= 10
        lngZoomLevel = acCmdZoom10
      Case Is <= 25
        lngZoomLevel = acCmdZoom25
      Case Is <= 50
        lngZoomLevel = acCmdZoom50
      Case Is <= 75
        lngZoomLevel = acCmdZoom75
      Case Is <= 100
        lngZoomLevel = acCmdZoom100
      Case Is <= 150
        lngZoomLevel = acCmdZoom150
      Case Is <= 200
        lngZoomLevel = acCmdZoom200
      Case Else
        lngZoomLevel = acCmdZoom200
    End Select
    ' Concatenate zoom constant and report name to
    ' one string variable to be passed to the form.
    strOpenArgs = CStr(lngZoomLevel) & Me.Name
    ' Open the form hidden.
    DoCmd.OpenForm cstrFormName, acNormal, , , , acHidden, strOpenArgs
  End If

End Sub

</code>

Form module:

<code>

Option Compare Database
Option Explicit

Private Sub Form_Timer()

  Static lngZoomFactor  As Long
  Static strReportName  As String
  Static booResized     As Boolean
  
  Dim lngReport         As Long
  
  If lngZoomFactor = 0 Then
    strReportName = Nz(Me.OpenArgs, vbNullString)
    If Len(strReportName) > 0 Then
      ' Extract zoom constant and report name.
      lngZoomFactor = Val(strReportName)
      strReportName = Mid(strReportName, Len(CStr(lngZoomFactor)) + 1)
    End If
    If Len(strReportName) = 0 Then
      ' Nothing to do.
      booResized = True
    Else
      ' Validate zoom constant.
      Select Case lngZoomFactor
      Case _
        acCmdZoom10, _
        acCmdZoom25, _
        acCmdZoom50, _
        acCmdZoom75, _
        acCmdZoom100, _
        acCmdZoom150, _
        acCmdZoom200, _
        acCmdFitToWindow
        ' Zoom factor/method accepted.
      Case Else
        ' Zoom constant cannot be used.
        ' Nothing to do.
        booResized = True
      End Select
    End If
  End If

  If booResized = False Then
    On Error Resume Next
    lngReport = Reports.Count
    If lngReport = 0 Then
      ' No reports are open.
      ' The report may have been printed without a preview.
      booResized = True
    ElseIf Reports(lngReport - 1).Name = strReportName Then
      ' The report is open. Resize it.
      DoCmd.SelectObject acReport, strReportName
      DoCmd.RunCommand lngZoomFactor
      If Err = 0 Then
        booResized = True
      Else
        ' Try to resize the report at next timer event.
      End If
    End If
    On Error GoTo 0
  End If
  
  If booResized = True Then
    ' Report has been resized or is gone.
    ' Stop timer but leave form open.
    ' Form will be closed by the OnClose event of the report.
    Me.TimerInterval = 0
  End If

End Sub

</code>

Have fun!

/gustav

>>> Gustav at cactus.dk 27-06-2007 12:06 >>>
Hi John

That's right. I ran my original test database in A97 and now it crashes when I try to print the preview. The same happened after conversion to A2003 and A2007, so this is not a version related issue. I suspect it being a printer driver or spooler issue, but no matter what I've tried, the crash occurs.

Anyone having a clue on what's going on? This is WinXP SP2 which I have had installed since 2005-02.

/gustav

>>> askolits at ot.com 26-06-2007 22:48 >>>
Here's the info. 

I have a snapshot of the error message but can't post it.
It's one of those 'Send Error report' messages

I grabbed some of your code from an accessd thread. The link is:
http://databaseadvisors.com/pipermail/accessd/2006-February/041825.html 


It works except, when I try to print, Access crashes.
It does not have a problem in print preview mode.
The app is Access 2000, but I can duplicate the roor on Access 2003 and 2000
and on different PCs.

Has anyone else had a problem using the code?

John