HELP! My Access Report is Missing Information When my Users Run It
Your Access report runs fine on your computer, but it's broken when an end user runs it. What gives?
You've spent hours designing a perfect Access report.
It's a two-part receipt with a store copy printed at the top and a customer copy printed at the bottom. The client can print the report on perforated paper and easily maintain a copy for their records. (You would be surprised how many regulated industries or government agencies still require maintaining physical copies of certain records.)
There's only one problem: it doesn't work on the user's machine.
Instead of seeing a duplicate receipt with the store copy on top and customer copy on the bottom, they see the store copy at the top of page 1, the customer copy at the top of page 2, and big empty spaces at the bottom of both pages.
Access's Automatic Margin Adjustment "Feature"
The most likely culprit is Access's automatic margin adjustment "feature."
Here's how it works:
Access reports will automatically resize margins based on the printer capabilities of the default printer. For example, if the report has a 0.5-inch bottom margin, but the printer requires a minimum 0.55-inch bottom margin, then Access will increase the bottom margin of the report to 0.55 inches at runtime.
When the bottom margin increases, the page no longer fits in the printable space.
The result is that the bottom section spills over onto the next page.
Fixing the Problem
There are several ways to approach fixing the problem.
Increase the report margins
The simplest fix is to increase the margins of your report to ensure that they work with the printable margins for each printer.
Update the printer driver
There is an outside chance that the operating system is not receiving accurate printable area dimensions from the printer hardware. This typically happens if the printer is using a universal driver. Switching to a model-specific driver may yield more accurate (and likely larger) printable area dimensions.
Save to PDF
Instead of printing directly to a physical printer, you can save the report to a PDF first.
You can do this using a virtual PDF printer, like PDFCreator or the now-built-into-Windows Microsoft Print to PDF, or use DoCmd.OutputTo with the acFormatPDF constant. Saving to PDF should avoid any "printable area" issues because there is no printable area to worry about.
You could still end up with a PDF that won't print full size on a printer with wide non-printable margins. However, most PDF viewers have an option to shrink the PDF to fit the printable area of the printer.
This adds steps to the process, so it's less than ideal. However, it is a fairly reliable workaround, especially as an "if all else fails" type of alternative.
Virtual Printers and Page Sizes
Not only are virtual printers a potential solution to this problem, they are also a potential cause!
Letter vs. A4
The US has several standard page sizes based on the imperial system:
- Letter: 8.5 x 11 inches
- Legal: 8.5 x 14 inches
- Ledger: 11 x 17 inches
Europe (and most of the rest of the world) uses page sizes based on the metric system:
- A4: 210 x 297 millimeters (8.3 x 11.7 inches)
- A3: 297 x 420 millimeters (11.7 x 16.5 inches)
Virtual printer default page sizes
Some virtual printers–and PDFCreator in particular–use a default page size that you may not be expecting.
While I create most reports based on standard Letter sizes (8.5 x 11 inches), the out-of-the-box default page size in PDFCreator is A4. Thus, when I open a report and PDFCreator is my default printer, Access will auto-adjust my page size to fit the A4 page.
Changing the page size from A4 to Letter is often not enough to completely fix the issue.
The problem is that Access will automatically adjust margins and control dimensions based on the physical capacity of the printer (whether physical or virtual), but only when the controls bleed into the non-printable areas.
Thus, the controls shrink to fit a smaller printable area, but do not automatically "re-expand" when the printable area expands.
Fixing the Problem
The easiest way to fix this problem is to adjust the default page size of the virtual printer before starting Access. Some printer settings may get cached when the application starts up or when the first report is opened.
It is possible that the report could get saved with the shrunken control dimensions.
If this happens on an end user computer, the simplest fix is to provide them with an updated copy of the front end.
If it happens on your development computer, you may need to manually fix the changes or import a previous version of the report. If you are using version control, this is easy to do. If not, it could be a bit of an adventure.