How to Create an Access Report for One Selected Table Record
Question details
The user needs a way to print all details of a currently selected database record, including fields spanning across multiple tabs or subforms, rather than printing only the active tab on the screen.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to print a comprehensive document for a specific record from a multi-tab form layout.
- Observed behavior
- The default form print function only outputs the currently visible tab page, failing to print the complete record data hidden in other tabs.
Ensure that your main form and its underlying data table have a clearly defined Primary Key, as this unique identifier is required to filter the report to the currently selected record.
Create a Dedicated Filtered Report using VBA
Design a dedicated report bound to your tables and use a command button on your form to trigger a VBA macro that opens the report filtered specifically for the active record.
Microsoft Access forms are optimized for on-screen data entry and display. When you attempt to print a form directly, Access merely prints the visual interface, which excludes hidden tabs. The best practice for printing database records is to separate presentation from output by creating a dedicated Report.
Navigate to the 'Create' tab and select 'Report Design'. Set the report's RecordSource to the table or query that contains all the required fields, including those spread across the form's various tabs.
Drag and drop the required fields into the Detail section of the report. If your form uses subforms for related data, insert corresponding Subreport controls into the main report and set their Master/Child linking properties appropriately.
You can filter the report directly by modifying its underlying query to include a WHERE clause referencing the open form (e.g., WHERE Invoices.InvoiceNumber = [Forms]![frmInvoice]![InvoiceNumber]).
Open your main form in Design View and add a Command Button. In the button's On Click event, add the following VBA code to open the report in preview mode: DoCmd.OpenReport "YourReportName", View:=acViewPreview
Looking for a Lightweight Alternative for Your Daily Office Tasks?
While Microsoft Access is a specialized relational database tool, most standard data organization, tracking, and reporting needs can be efficiently handled using WPS Office. As a comprehensive and free alternative to MS Office, WPS Office offers powerful Spreadsheets, Document, and Presentation tools.
- 1. Download WPS Office: Visit the official WPS website and download the free setup file.
- 2. Install the Software: Run the downloaded installer and follow the on-screen prompts to complete the installation.
- 3. Analyze Exported Data: Open WPS Spreadsheet to import your exported Access database tables (.csv or .xlsx) and utilize pivot tables for comprehensive reporting.
Frequently Asked Questions
Why does Access only print the active tab of my form?
Forms in Access are rendered as screen objects. When you issue a print command on a form, Access prints exactly what is currently rendered on your monitor, essentially taking a screenshot, which ignores data hidden on unselected tabs.
How do I print the report directly without previewing it?
To bypass the print preview and send the report directly to your default printer, change the View argument in your VBA code from acViewPreview to acViewNormal (e.g., DoCmd.OpenReport "YourReportName", View:=acViewNormal).
Can I filter the report using VBA without modifying the report's query?
Yes. Instead of hardcoding the form reference in the query, you can use the WhereCondition parameter in the OpenReport command: DoCmd.OpenReport "YourReportName", acViewPreview, , "InvoiceNumber = " & Me.InvoiceNumber.
