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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)