Microsoft Access Report Tips and Avoiding 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.
  ' 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.

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.

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.

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.

About the Author

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.