Filtered by Form Reports

Sometimes you need to print a report with data for the current form record. You may be tempted to print the form, but this is rarely workable. Well-designed forms have controls that don’t make sense on reports, such as command buttons, option groups, listboxes, and comboboxes; and they generally have colored backgrounds and control special effects that don’t print well. Additionally, forms are generally designed to display one data record at a time, while reports (with the exception of some name and address reports) are usually designed to display data from multiple records on one page. If you print a form filtered for the current record, you’ll get a report that looks like Figure 6.25.

Figure 6.25

The form header and footer print, though the combobox and command button are meaningless on a report, and the subforms aren’t sized properly. What you need here is a report that prints the data for a single form record in a format suitable for printing. (Printing a report for each order, as it is entered, ensures that there is a paper record of orders, in case of a power outage or other disaster.) In many cases, you can use the same report to print a single record as for printing multiple records, by just applying a filter so the report prints only the current record. However, in this case it’s best to create a report with a different format, to give full details about the individual order in a compact format. The single-record report is shown in Figure 6.26. (This report is opened by clicking the Print Report command button in the footer of the fpriOrders form.)

Figure 6.26

The report’s caption is set to Order No. n by the code, and you can see this caption in the report’s title bar in print preview, but curiously, the textbox with the control source =[Caption] is not displayed correctly in print preview, although it does print correctly. If this isn’t acceptable, you can change the control source of txtCaptionRH to =”Order No. “ & [OrderID]; this will make it display correctly in print preview, as well as on the printed report. (I did this for the sample report.)

A report designed to print just a single order generally doesn’t need a page header; if it will never be longer than one page, it may not need a page footer either (though if you want to see the date and time the report was printed, the page footer is a good place for this information). Similarly, there is no need for a report footer. There is no need for report groups because you only need to sort and group records if you are printing multiple records, so all the data can be displayed in the Detail section. All of these requirements make for a report that is formatted very differently than a grouped report intended to print data from multiple records.

There are a variety of ways to filter a report by the current form record. One is to use the form’s key field as a criterion in the report’s record source; this is the method I use for the Single Order report shown in Figure 6.26. This method is suitable for a special report only used to print data from a single selected record. You can also create a filter string in code and use it to set a report’s Filter property, as in the code segment listed below. This method is more suitable when the same report is used for printing both a single record and multiple records.

 Private Sub cmdPrintOrderAlternate_Click() On Error GoTo ErrorHandler        Dim lngOrderID As Long    Dim strCaption As String    Dim strReport As String    Dim rpt As Access.Report    Dim strFilter As String        lngOrderID = Nz(Me![OrderID])    If lngOrderID = 0 Then       GoTo ErrorHandlerExit    Else       strReport = "rptOrdersGroupedV3"       strCaption = "Order No. " & lngOrderID       strFilter = "[OrderID] = " & lngOrderID    End If        DoCmd.OpenReport reportname:=strReport,        View:=acViewPreview,        windowmode:=acHidden    Set rpt = Reports(strReport)    rpt.Caption = strCaption    rpt.FilterOn = True    rpt.Filter = strFilter    DoCmd.OpenReport reportname:=strReport,        View:=acNormal     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The Single Order report is opened by code on a command button, which follows. The code first checks whether there is an Order ID and exits the procedure if none is found (this indicates a new record, not yet filled in). Then, a variable is set to the Single Order report name, and another variable is created for the report caption, with the current order number. The report is opened in hidden mode, the caption is set, and then it is printed.

 Private Sub cmdPrintOrder_Click() On Error GoTo ErrorHandler        Dim lngOrderID As Long    Dim strCaption As String    Dim strReport As String    Dim rpt As Access.Report        lngOrderID = Nz(Me![OrderID]) If lngOrderID = 0 Then       GoTo ErrorHandlerExit    Else       strReport = "rptSingleOrder"       strCaption = "Order No. " & lngOrderID    End If        DoCmd.OpenReport reportname:=strReport,        View:=acViewPreview,        windowmode:=acHidden    Set rpt = Reports(strReport)    rpt.Caption = strCaption    DoCmd.OpenReport reportname:=strReport,        View:=acNormal     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub