
	Quick Find:		Search for:

Free Resources from FMS
Demos Email Newsletter Technical Papers Tips and Techniques Links Book Recommendations Microsoft Access Resource Center



"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - *Darren D.*

Microsoft Access Report Tips and Avoiding Common Mistakes

by Luke Chung
President of FMS

Introduction

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.

Common Mistakes

Check these items on your reports before you distribute them:

Use Captions

Without setting the report caption property, the name of the report appears which may not be what you want the user to see.

Spell Check Labels and Captions

Make sure that what users see on the labels and captions is accurate and spelled correctly.

Verify Help Context IDs

If you are using a help file, make sure the help file name and help context ID are correct.

Avoid Missing Code

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.

Verify GroupHeader KeepTogether Property is not No

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.

Set AutoCenter Property to Yes

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.

Use NoData Event to Handle Reports with No Data

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.
' 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 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.

Use One Routine to Handle All Report Printing and Previewing

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.

Additional Resources

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.

The first is Total Access Analyzer from FMS, Inc. 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.

The second tool, Total Access Speller, is also from FMS, Inc. Total Access Speller is a spell-checker that spell checks what the user sees on your forms, reports, and other objects. Easily verify all the captions and labels are properly spelled.

Conclusion

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.

Author: Luke Chung, President and Founder of FMS, Inc. (www.fmsinc.com) FMS is 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.

Back to Main Technical Papers Page