Module: Reports in Category Microsoft Access : Reports from Total Visual SourceBook

Manage Microsoft Access reports to design, preview, print, report view, output to PDF, and other formats with Access VBA.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modReports module.
CurrentReportView Procedure Retrieve the current view of the specified report, if it's open.
GetActiveReport Procedure Get the name of the currently active report.
IsReportOpen Procedure Determine if a report is open.
IsReportOpenInDesignView Procedure Determine if a report is open in design view.
IsReportOpenInLayoutView Procedure Determine if a report is in report view mode.
IsReportOpenInPrintPreview Procedure Determine if a report is in print preview mode.
IsReportOpenInReportView Procedure Determine if a report is in report view mode.
IsReportOpenInMode Procedure Determine if a report is open in a particular view.
ReportClose Procedure Close the named report without errors by first checking if the report is open, and closing it if so. This is useful to close a report that isn't open without crashing.
ReportCloseAll Procedure Close all open reports that can be closed.
ReportControlPropertiesChange Procedure Set a property to a value on all controls of a report. Opens a Report in design view, sets the specified property to the specified value on all controls, and optionally saves the Report.
ReportControlsToArray Procedure Populate the passed array with control names of a Report. The passed array must be 0-based. The procedure will expand the array as needed, so you do not need to pre-allocate array storage before calling the procedure. If the report specified is not open, the procedure opens it in design view, fills the array and then closes the report. If the report specified is already open, this procedure does not re-open or close the report.
ReportControlsToString Procedure Populate the passed string with control names of a Report. Use the chrDelimit parameter to specify the character or characters to use as the delimiter between control names. The procedure places the resulting string in the strIn parameter.
ReportNamesToArray Procedure Load an array with the names of all the reports in the current database.
ReportNamesToString Procedure Populate a string with the names of all the reports in the current database.
ReportOpen Procedure Open or set focus to a report.
ReportOpenDesign Procedure Open a report in design mode. If it's already open, change the current view to design view.
ReportOpenDesignAll Procedure Open all the reports in design view and minimize them on the workspace. Skips reports that are already open.
ReportPropertyChange Procedure Change the value of a report property.
ReportsOpen Procedure Determine if any reports are open.
ReportOpenPreview Procedure Open a report in preview mode and maximize it (useful if user doesn't have tab view).
ReportOpenReportView Procedure Open a report in report view mode, maximized (useful if user doesn't have tab view).
ReportOutput Procedure Export a report to a file such as a PDF, XPS, HTML, etc.
ReportOutputPDF Procedure Export a report to a file in PDF format.
ReportPrint Procedure Print an entire report.
ReportPrintPages Procedure Print a range of pages from a report.
WaitUntilReportClose Procedure Wait for a report to close as an alternative to opening the report in dialog mode. Uses Sleep command rather than DoEvents which eats up CPU processing resources.
' Example of modReports
'
' To try this example, do the following:
' 1. Create a new form in your copy of Sample.mdb (in the Total Visual SourceBook sample directory).
' 2. Create a command button 'cmdTest'
' 3. Paste this code into the form's module

#If VBA7 Then
  Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#Else
  Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#End If

Private Const mcstrReportName As String = "rptInvoice"

Private Sub Form_Open(Cancel As Integer)
  cmdTest.Caption = "Test Access report functions"
End Sub

Private Sub cmdTest_Click()
  Dim strResult As String
  Dim intCount As Integer
  Dim intCounter As Integer
  Dim astrNames() As String
  Dim strNames As String
  Dim strFileName As String
  Dim fOK As Boolean

  ' Preview a report
  fOK = ReportOpenPreview(mcstrReportName)
  Debug.Print "Preview report: " & fOK

  ' Determine which report is the current one
  strResult = GetActiveReport()
  Debug.Print "Active report: " & strResult

  ' Determine if a report is open
  fOK = IsReportOpen(mcstrReportName)
  Debug.Print mcstrReportName & " is open " & fOK

  ' Determine which mode a report is open in
  If IsReportOpenInDesignView(mcstrReportName) Then
    Debug.Print mcstrReportName & " is open in design view"
  ElseIf IsReportOpenInLayoutView(mcstrReportName) Then
    Debug.Print mcstrReportName & " is open in layout view"
  ElseIf IsReportOpenInPrintPreview(mcstrReportName) Then
    Debug.Print mcstrReportName & " is open in Print Preview"
  ElseIf IsReportOpenInReportView(mcstrReportName) Then
    Debug.Print mcstrReportName & " is open in report view"
  ElseIf IsReportOpenInMode(mcstrReportName, acViewPivotChart) Then
    Debug.Print mcstrReportName & " is open in Pivot Chart view"
  End If

  ' Close report
  fOK = ReportClose(mcstrReportName)
  Debug.Print "Report closed " & fOK

  ' Open a report in design mode
  fOK = ReportOpenDesign(mcstrReportName, True)
  Debug.Print "Report opened in design mode " & fOK

  ' Determine the current report view
  Debug.Print "Current report view is: " & CurrentReportView(mcstrReportName)
  ReportClose mcstrReportName

  ' Open a report in Review View
  fOK = ReportOpenReportView(mcstrReportName, True, acSaveYes)
  Debug.Print "Current report view is: " & CurrentReportView(mcstrReportName)
  ReportClose mcstrReportName

  ' Open all reports in design mode
  intCount = ReportOpenDesignAll(acHidden)
  Debug.Print "Reports opened: " & intCount

  ' Change the caption property of a report
  intCount = ReportControlPropertiesChange(mcstrReportName, "Caption", "New Caption", False)

  ' Get all the controls of a report in an array
  intCount = ReportControlsToArray(mcstrReportName, astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print intCounter & ": " & astrNames(intCounter)
  Next intCounter

  ' Get all the controls of a report into a string
  intCount = ReportControlsToString(mcstrReportName, ";", strNames)
  Debug.Print "Control Names: " & strNames

  ' Get all the report names into an array
  intCount = ReportNamesToArray(astrNames)
  For intCounter = 0 To intCount - 1
    Debug.Print intCounter & ": " & astrNames(intCounter)
  Next intCounter

  ' Get all the report names to a string
  intCount = ReportNamesToString(strNames, ";")
  Debug.Print "Report Names: " & strNames

  ' Open a report in preview mode
  If ReportOpen(mcstrReportName, acViewPreview) Then
    Sleep 1000
    fOK = ReportClose(mcstrReportName)
    Debug.Print "Report closed: " & fOK
  End If

  ' Example of ReportCloseAll
  fOK = ReportCloseAll(True)
  Debug.Print "All reports closed: " & fOK

  ' Example of ReportPropertyChange
  fOK = ReportPropertyChange(mcstrReportName, "Caption", "New Caption", True)
  Debug.Print "Report property changed " & fOK

  ' Example of ReportsOpen
  fOK = ReportsOpen()
  Debug.Print "Reports are open " & fOK

  ' Example of ReportOutput
  If MsgBox("Export report to txt?", vbYesNo) = vbYes Then
    strFileName = InputBox("Enter full export path for the txt export (e.g. C:\Tmp.txt):")
    fOK = ReportOutput(mcstrReportName, "txt", strFileName, True)
    Debug.Print "Report Output: " & fOK
  End If

  ' Example of ReportOutputPDF
  If MsgBox("Export report to PDF?", vbYesNo) = vbYes Then
    strFileName = InputBox("Enter full path for PDF export.")
    fOK = ReportOutputPDF(mcstrReportName, strFileName, True)
    Debug.Print "PDF Output: " & fOK
  End If

  If MsgBox("Do you want to print a report?", vbYesNo) = vbYes Then
    fOK = ReportPrint(mcstrReportName)
    Debug.Print "Report printed: " & fOK
  End If

  If MsgBox("Do you want to a subset of report pages?", vbYesNo) = vbYes Then
    fOK = ReportPrintPages(mcstrReportName, 2, 3)
    Debug.Print "Printed a subset of report pages: " & fOK
  End If

  ' Preview a report and wait until the user closes it
  DoCmd.OpenReport mcstrReportName, acViewPreview
  MsgBox "Report is currently being previewed"

  WaitUntilReportClose mcstrReportName
  MsgBox "Report preview is closed"

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS