Home	Excel	Access
\| 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

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_BtnPreview_Click:
      

    Exit Sub
      

      

      Err_BtnPreview_Click:
      

    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

Page last updated Apr14