Access reports are extremely powerful and considered by many to be the best report generator for Windows. However, the developer can easily make mistakes that cause a report to behave incorrectly or poorly. Hereís a checklist for common problems with report designs. Developers should verify these problems do not exist prior to finalizing their reports.
Check these items on your reports before you distribute them:
Without setting the report caption property, the name of the report appears which may not be what you want the user to see.
Make sure that what users see on the labels and captions is accurate and spelled correctly.
If you are using a help file, make sure the help file name and help context ID are correct.
Make sure every event that has [Event Procedure] assigned actually has an event procedure defined. A common mistake is to assign the event without actually clicking through to write the code for it. Section level events should also be checked.
For each group header, make sure its KeepTogether property is set to "With First Detail" or "Whole Group", rather than No. This prevents the group header from being printed at the bottom of a page by itself without any detail records. Setting this property correctly shifts the header to the next page so it wonít appear alone. Avoid using "Whole Group" if the size of your group exceeds one page since it forces a blank page before printing the data.
For Access 2002 and later, make sure the AutoCenter property is set to Yes. AutoCenter makes sure that when your report is opened, it opens in the center of the screen regardless of where you placed it when you saved it on your machine, or what the userís screen resolution is.
Make sure each report has a NoData event to handle situations when thereís no data. You may think your report will always have data, but even in these cases, you should handle the possibility of No Data so the report can support filtering. This is especially important if you want to print several reports at one time (in a batch) and don't want one empty report to stop the entire process. Fortunately, the NoData event lets you handle this. It can set a public Boolean variable, cancel the report, and let the calling procedure display a message gracefully informing the user thereís no data in the report.
For example, this is what is in the reportís NoData event:
Private Sub Report_NoData(Cancel As Integer) gfReportHasNoData = True Cancel = True End Sub
where gfReportHasNoData is a public variable defined in the declarations section of a module like this:
Public gfReportHasNoData As Boolean
The code that previews or prints the report would be similar to this:
Public Function PrintPreviewReport(pstrRptName As String, pfPreview As Boolean) As Boolean ' Comments : Print or preview a report. Waits for completion. ' Params : pstrRptName Name of report to print/preview ' pfPreview True to preview, False to print ' Returns : True if successful, False if not. Dim fOK As Boolean Dim lngSaveErr As Long Dim intDoEvents As Integer fOK = True ' Becomes True if report has no data gfReportHasNoData = False ' Preview or print the report On Error Resume Next lngSaveErr = 0 If pfPreview Then DoCmd.OpenReport pstrRptName, acViewPreview lngSaveErr = Err.Number If Not gfReportHasNoData Then ' Set focus to the report and maximize it DoCmd.SelectObject acReport, pstrRptName, False DoCmd.Maximize End If Else DoCmd.OpenReport pstrRptName, acViewNormal lngSaveErr = Err.Number End If fOK = (lngSaveErr = 0) On Error GoTo 0 ' Did report print or preview successfully? If gfReportHasNoData Then MsgBox "Report has no data." End If PrintPreviewReport = fOK End Function
Therefore, if the report has no data, rather than showing or printing the report with no records (data), a message box appears and informs the user there was no data.
Rather than scattering DoCmd.OpenReport code around your application, create one procedure like the PrintPreviewReport above. This lets you centralize all the interactions with reports, trap for errors, etc. A more generic procedure would let you pass parameters that correspond to the OpenReport parameters such as the Filter/Where clause, and OpenArgs if you use that.
Making sure reports are properly designed takes time and effort to verify all the things that could go wrong. This is especially difficult if you are taking over someone elseís work and/or if there are a large number of reports. Fortunately, there are some tools which can address some of the pitfalls listed here by examining all your reports and highlighting the problems.
Total Access Analyzer is the most popular Access add-in and offers comprehensive database analysis that covers much more than reports. With the exception of verifying the accuracy of what users see, the report analysis detects all the other pitfalls listed here plus many other areas such as module code analysis, procedure flow diagrams, cross-reference documentation and verification, and much more.
Total Visual SourceBook is a royalty-free source code library that includes 125,000+ lines of code that you can add to your Access applications. There is extensive code to create, modify and output reports, plus code to handle many other aspects of application development. Extensively documented with comments and examples, Total Visual SourceBook is great for increasing your productivity.
Total Access Speller is a spell-checker that verifies what the user sees on your forms, reports, and other objects. Easily verify all the captions, labels, status bar text, validation text, etc. are properly spelled.
Developing an application takes a great deal of effort. Donít let simple mistakes ruin your application and embarrass you in front of your boss and/or clients. Even a simple typo can ruin the trust a user has for your application, work, and attention to detail. It usually only takes a few minutes per report to ensure those who use it donít see obvious problems. This is a wise investment of your time for your career and reputation. Use this checklist as the basis of your quality assurance process for your reports. If you find other problems, add them to the list so you can avoid them in the future. Making mistakes is okay. Making the same mistake more than once means youíre not learning. Good luck.
Luke Chung, President and Founder of FMS, Inc., the leading provider of 3rd party products for Microsoft Access users and developers. Visit the FMS web site for additional Access resources, utilities, technical papers, demos, and product information.
Version 16.1 for
Microsoft Access 2016
Version 15.6 for
Microsoft Access 2013
Version 14.6 for
Microsoft Access 2010
Version 12.96 for
Microsoft Access 2007
Version 11.95 for
Microsoft Access 2003
Version 10.8 and 9.8 for Access 2002 and 2000