Printing Problems

How to Print Only One Record or Chart Set in Access Reports

Maira Mehtab Oct 1, 2026 868 views

Question details

The user needs to restrict an Access report to print only the very first record or chart set, preventing duplicates for subsequent grouped items.

How to Print Only One Record or Chart Set in a Microsoft Access Report

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating and printing a grouped report that includes charts or records where only the first data set is required on the printout.
Observed behavior: The report automatically prints duplicate records or chart sets for every selected grouped item, such as every month, instead of stopping after the first one.

Before you start

Before modifying your report design and adding VBA code, create a backup copy of your Microsoft Access database to prevent any unintended data or layout loss.

Solution 1Recommended

Use a Hidden Counter Text Box and VBA Code

Add a running sum counter to the report's Detail section and utilize a simple VBA script in the On Format event to cancel the printing of all subsequent records.

By leveraging the Running Sum property, you can create a customized counter that tracks how many records have been formatted. Once the counter exceeds 1, VBA code can cancel the formatting event, effectively stopping the report from rendering duplicate charts or records.

Open Design View

Open your Microsoft Access database, right-click the target report in the navigation pane, and select 'Design View'.

Add a hidden text box

Add a new Text Box control to the report's Detail section. Name this text box 'txtCounter' and set its 'Visible' property to 'No' so it does not appear on the final printout.

Configure text box properties

Open the Property Sheet for the text box. Set its 'Control Source' property to '=1' and change the 'Running Sum' property to 'Over All'.

Locate the On Format event

Click on the background of the Detail section to select it. In the Property Sheet, navigate to the 'Event' tab and locate the 'On Format' property.

Open Code Builder

Click the ellipsis (...) button next to 'On Format' and select 'Code Builder' to open the VBA editor.

Insert the VBA code

Within the generated event procedure, type the following code: Cancel = Me.txtCounter > 1. Save your changes and switch to Print Preview to verify the results.

Use a Hidden Counter Text Box and VBA Code

Property Sheet Navigation: Make sure you are looking at the 'Event' tab in the Property Sheet and specifically selecting 'On Format'. Do not confuse this with the general 'Format' tab, which handles visual styling.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Alternative?

While WPS Office does not include a database management tool like Access, it is an excellent free alternative for handling your daily word processing, spreadsheet, and presentation tasks. Enjoy a familiar interface and seamless compatibility with Microsoft Office documents.

1. Download the Installer: Visit the official WPS Office website and click 'Download' to get the free installation package.
2. Install WPS Office: Run the downloaded installer file and follow the on-screen prompts to complete the quick setup.
3. Open Your Office Files: Launch WPS Office and directly open your existing Word, Excel, or PowerPoint files without losing any formatting.

Free and lightweight office suite for daily productivityFully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx)Familiar, easy-to-use interface requires no learning curveIncludes powerful built-in PDF editing and conversion tools

microsoft office alternative - wps office

Frequently Asked Questions

Can I apply this method if my charts are in a Group Header instead of the Detail section?

Yes. If your charts or records are located in a Group Header, place the hidden 'txtCounter' text box in that specific header section and apply the VBA code to the Group Header's 'On Format' event rather than the Detail section.

Why does my Access report print blank pages after the first record?

This usually happens when the report's layout width exceeds the printable page width, or margins are too wide. Even though the VBA code stops the data from printing, Access may still generate blank space. Ensure your report width plus left and right margins do not exceed your paper size.

What exactly does the 'Running Sum' property do in this context?

Setting 'Running Sum' to 'Over All' turns the hidden text box into a counter. Because its Control Source is '=1', it adds 1 for every record Access processes in the report. This allows the VBA code to easily identify when the very first record (counter = 1) has finished printing.

Document & File Issues