Home	Excel	Access
| Feedback | A report template |

Access database - a reports manager form

A form displaying a selection of reports and giving the user a choice of destination

Access contains a helpful wizard which allows you to place a button on a form that will open a report. If you have several reports you may wish to create a reports manager which lists them and provides a choice of displaying, printing or saving them to disk.

In this example, design a simple form containing two Option Groups. The left hand group shows the various reports made available to the user and the right hand group gives a choice of destination. If you have a large number of reports you can use the Tab Control with a separate Option Group on each tab. These tabs could all share a single destination Option Group.

The RTF option generates a report file in Rich Text Format. This exports a file to disk which is similar to the view in Access except that lines, boxes and pictures are omitted. It can be read using Word or many other programs. The SNP option creates a report file in Snapshot format. Snapshot is a nice program, freely available from Microsoft which allows a user to view an image of a previously saved Access report (including graphics).

Each of the Option Groups can be created using the design wizard. Add a button to the form and link the following VB code to the button's On Click event.

Private Sub BtnPreview_Click() On Error GoTo Err_BtnPreview_Click Dim stDocName As String, FName As String ' FrameReports is the Option group containing each of the report names Select Case FrameReports.Value Case 1 stDocName = "budget report cc" Case 2 stDocName = "budget report dir" Case 3 stDocName = "budget report trust dir" Case 4 stDocName = "budget report cc single" Case 5 stDocName = "direct totals" Case 6 stDocName = "cc vals for direct" End Select ' MyFormat is the Option group containing the report destinations If myformat.Value > 2 Then FName = "C:\My Documents\Access\" FName = FName & InputBox("What do you wish to call your file?", _ "Reports 2", "anyfile") End If Select Case myformat.Value Case 1 DoCmd.OpenReport stDocName, acViewPreview Case 2 DoCmd.OpenReport stDocName, acViewNormal Case 3 FName = FName & ".rtf" DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, FName Case 4 FName = FName & ".snp" DoCmd.OutputTo acOutputReport, stDocName, "snapshot Format", FName End Select Exit_BtnPreview_Click: Exit Sub Err_BtnPreview_Click: MsgBox Err.Description Resume Exit_BtnPreview_Click End Sub

Would you like to learn more? Microsoft Office Access 2003 Step by Step (with CD)

A study by accountants Coopers and Lybrand has shown that 90% of spreadsheets with more than 150 rows contain errors. Check out my information on creating well designed spreadsheets.