Hack 35. View Reports Embedded in Forms
Preview reports, whether current or historical, directly on the form you are working on.
Access is one of the most powerful reporting tools on the market. Beginning with Access 97, Microsoft introduced the ability to create snapshot reports that you can view with the free Snapshot Viewer, available for download from Microsoft (search for ActiveX Snapshot Viewer at http://www. microsoft.com/downloads).
4.9.1. Creating the Form
The form is composed of a combo box and the ActiveX Snapshot Viewer. The combo box contains a list of all the reports in the database. When the form opens, the Load event executes the following code to fill the combo box with a listing of all available reports:
Private Sub Form_Load( ) Dim obj As AccessObject, dbs As Object Dim strList As String Set dbs = Application.CurrentProject For Each obj In dbs.AllReports strList = strList & obj.Name & ";" Next obj cboReports.RowSourceType = "Value List" cboReports.RowSource = strList End Sub
To add the ActiveX Snapshot Viewer, select More Controls from the toolbox, as shown in Figure 4-28, scroll down, and select Snapshot Viewer Control 11.0. Note that depending on your version of Access, your control might be earlier than the 11.0 version.
Figure 4-28. Adding the ActiveX Snapshot Viewer
Size the Snapshot Viewer Control to an approximate width that matches the size of the paper on which you will print the report. This helps avoid having to scroll left and right to see a report once it is displayed. Figure 4-29 shows the form design with the Snapshot Viewer Control in place.
Figure 4-29. Sizing the snapshot viewer
After adding the Snapshot Viewer Control, place the following code in the On Change event of the combo box. Make sure the name of the Snapshot Viewer on your form matches the name in the code:
Private Sub cboReports_Change() DoCmd.OutputTo acOutputReport, cboReports, acFormatSNP, _ Application.CurrentProject.path & "\temp.snp" SnapshotViewer1.SnapshotPath = _ Application.CurrentProject.path & "\temp.snp" End Sub
In this example, a temporary snapshot report is created, called temp.snp, and it is placed in the directory in which the database is running. The temp.snp snapshot report is then loaded into the Snapshot Viewer. This temp.snp file is replaced each time a new selection is made. If you are running from a shared location with a multiuser database, make sure you store the temporary snapshot file on the local machine, not on the network; this avoids any multiuser issues.
As shown in Figure 4-30, the final form displays an invoice report that was selected from a combo box.
Figure 4-30. Displaying a report
This hack gives the application one place for users to select reports and view them before printing. Also note that the ActiveX Snapshot Viewer includes a Print button next to the navigation buttons.
4.9.2. Hacking the Hack
An advantage to saving reports as snapshots is they are static, as opposed to the dynamic reports you get with report objects in Access. You can save those reports in a folder and view them as historical reports with the Snapshot Viewer. You also can change the code to have a combo box that displays the report names from a given directory, which allows the user to select historical reports to view with the Snapshot Viewer.