databasedev.co.uk - database solutions and downloads for microsoft access

Printing the Record on the Microsoft Access Form to a Report:

In a previous article, we detailed How To Print A Single Record from a Form into a Report and gave the common syntax that would be required to do this. The syntax uses the WHERE clause in the DoCmd.OpenReport method shown below:

DoCmd.OpenReport reportname [, view][, filtername][, wherecondition]

In this article we put this in to practice and show an example of how this can be implemented.

Imagine that we have a database that stores contact information for all employees that work for a company. There may be a time when we need output this information to a Microsoft Access Report, although at times we may only need to view information for a certain record.

From the example form shown below, we are able to print a report of the current Salesperson:

Behind the Preview Report command button we use the following code:

Private Sub cmdPrintPreview_Click()

    Dim strReportName As String
    Dim strCriteria As String

    If NewRecord Then
        MsgBox "This record contains no data." _
             , vbInformation, "Invalid Action"
        Exit Sub
    Else
        strReportName = "rptSalespersonContact"
        strCriteria = "[lngSalespersonID]= " & Me![lngSalespersonID]


        DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End If
End Sub

This will preview the report where the SalespersonID of the report matches the SalespersonID of that visible on the report.

If the form is on a new record or data has not been saved in that record a message box will be shown and the report not previewed.

This example uses the SalespersonID, which is the Primary Key in the table and which is a numeric value. If your primary key is a Text type field and not a numeric field, you need extra quotes in the linking criteria:

strCriteria = "[lngSalespersonID]='" & Me![lngSalespersonID] & "'"

If you wish to print the report, without previewing it, change the acViewPreview with acViewNormal in the DoCmd.OpenReport statement

To see an example of this, please download the Microsoft Access 2000 sample database file from either the Microsoft Access Reports menu or the Microsoft Access Downloads menu.