Access Report Tips: Pitfalls to AvoidThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
April 2002, Revised June 2002
Microsoft® Access (2.0, 95, 97, 2000, 2002)
Summary: This article offers tips to developers to help them avoid some of the pitfalls when creating Access reports. This article applies to Microsoft Access databases (.mdb) and Microsoft Access projects (.adp). This article assumes knowledge of creating Access reports.
Access reports are extremely powerful, and Access is considered by many to be the best report generator for Microsoft Windows®. However, the developer can easily make mistakes that can cause a report to behave incorrectly or poorly. What follows is a checklist for common problems with report designs. Developers should verify that these problems do not exist prior to finalizing their reports.
Check these items on your reports before you distribute them:
- Use a report caption. Without a caption, the name of the report appears, which may not be what you want the user to see.
- Make sure that what the user sees 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. This can also happen if you rename a control and forget to rename the event procedures tied to the old name. Section-level events should also be checked.
- For each group header, make sure the 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, because it forces a blank page before printing the data.
- For Access 2002, 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 computer, or regardless of the user's screen resolution.
- Make sure each report has a NoData event to handle situations when there's no data. The NoData event should set a Public Boolean variable and cancel opening the report. The calling procedure should post a message, gracefully informing the user that no data exists in the report.
For example, this is an example of code to include in the report's NoData event:
Private Sub Report_NoData(Cancel As Integer) gfReportHasNoData = True Cancel = True End Sub
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. ' In : pstrRptName - Name of report to print/preview ' pfPreview - True to preview, False to print ' Out : 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 fOK Then If pfPreview Then ' Wait for report to close. Do While (SysCmd(acSysCmdGetObjectState, acReport, pstrRptName) > 0) For intDoEvents = 1 To 3 DoEvents Next intDoEvents Loop End If ElseIf 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.
Ensuring that 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 or if you have a large number of reports. Fortunately, there are some tools that can address some of the pitfalls listed here by examining all your reports and highlighting the problems.
One tool is http://www.fmsinc.com/Products/analyzer/index.html from FMS, Inc. Total Access Analyzer is a comprehensive Access database analysis tool that covers much more than reports. With the exception of verifying the accuracy of what users see, the report analysis in Total Access Analyzer detects all the other pitfalls listed here. Total Access Analyzer is available for Access 2.0, 97, 2000, and 2002.
The second tool, http://www.fmsinc.com/Products/speller/index.html, is also from FMS, Inc. Total Access Speller is a spell-checker that checks what the user sees on your forms, reports, and other objects. You can easily verify that all captions and labels are properly spelled. Total Access Speller is available for Access 2.0, 97, 2000, and 2002.
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 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 that 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 is President and Founder of FMS Inc., a leading provider of third-party products for Microsoft Access users and developers.