Printing and Viewing Access Reports (VBA)

[rdove]

This code enables you to specify a Microsoft Access database open a report and print it out or open in preview form through Visual Basic.

Create a module and place the following inside:

'Code written by Ryan Wischmeyer

'This code is intended for educational use

'and may not be implemented into projects

'which are to be sold for retail value.

Option Explicit

    ' In other applications like Microsoft Visual Basic,

    ' you can include a reference to Microsoft Access to

    ' gain the use of Access constants. Or, use the following

    ' constant values...

    Global Const acNormal = 0

    Global Const acDesign = 1

    Global Const acPreview = 2

    ' -----------------------------------------------------

    ' Application Quit options...

    ' saves all objects without displaying a dialog box:

    Global Const acSaveYes = 0

    ' displays a dialog box that asks whether you want to save any

    ' database objects that have been changed but not saved:

    Global Const acPrompt = 1

    ' quits Microsoft Access without saving any objects:

    Global Const acExit = 2

    Sub PrintAccessReport(dbname As String, rptname As String, preview As Boolean)

    Dim objaccess As Object

    On Error GoTo PrintAccessReport_ErrHandler

    Set objaccess = CreateObject("Access.Application")

    With objaccess

        .OpenCurrentDatabase filepath:=dbname

        If preview = True Then 'Preview report on screen.

            .DoCmd.OpenReport reportname:=rptname, View:=acPreview

            .Visible = True

            .DoCmd.Maximize 'maximizes the Report window

            .DoCmd.Maximize 'maximizes Access window

        Else 'Print report to printer.

            .DoCmd.OpenReport reportname:=rptname, View:=acNormal

            DoEvents 'Allow report to be sent to printer.

        End If

    End With

    Set objaccess = Nothing

    Exit Sub

    PrintAccessReport_ErrHandler:

        MsgBox Error$(), , "Print Access Report"

    End Sub

Now Call this procedure with a command button or whatever replacing the example below with the desired parameters. The first parameter requests the path to the database, the second requests the Report name, and the third requests the view state: True will open access and display the report, False will just print the report.

Call PrintAccessReport App.Path & "collectables.mdb", "Figures", True