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.
' 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.
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.
|
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
100,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.
|
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.
About the 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.
|
