Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!
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