| Feedback | A report template | 20 things to do with a Query |

Access database - a reports manager form

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

report manager form

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 XPS option generates a report file which can be shared by other Windows users. The program code can be easily modified to choose PDF or Rich Text Formats. Look at the program help for the DoCmd.OutputTo command to see the various options.

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, stFileName As String

Select Case FrameRepNames.Value
Case 1
 stDocName = "usergrps_simple"
Case 2
 stDocName = "usergrps_emails"
Case 3
 stDocName = "template"
Case 4
 stDocName = ""
End Select

Select Case OutputOptions.Value
Case 1
' open a print preview
DoCmd.OpenReport stDocName, acPreview
Case 2
' send to default printer
DoCmd.OpenReport stDocName, acNormal
Case 3
' export to a file
stFileName = "C:\MyData\" & stDocName & "_" & Format(Time(), "mmss") & ".xps"
DoCmd.OutputTo acOutputReport, stDocName, "XPSFormat(*.xps)", stFileName, False, "", , acExportQualityPrint
MsgBox "The report has been saved as:" & vbCrLf & stFileName, vbOKOnly
End Select

Exit Sub

MsgBox Err.Description
Resume Exit_BtnPreview_Click

End Sub

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.

file: areptmngr.htm 2014 Page last updated Apr14