Microsoft KB Archive/132881

= ACC: How to Base a Report on a Recordset Object =

Article ID: 132881

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q132881



SUMMARY
This article demonstrates how you can use a Recordset object in Visual Basic for Applications as the record source for a report.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access for Windows 95" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access for Windows 95 version 7.0) is called Access Basic in version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
In Microsoft Access, you can set a report's RecordSource property only to table, query, or SQL statement. However, using a report not based on any table or query and Visual Basic for Applications (or Access Basic) code, you can set the report's record source to a Recordset object.

NOTE: For the method described in this article to work successfully, you must use a report that meets the following conditions:
 * The report's Width property is set to 6.5 inches.
 * The report header section's Height property is set to 0 inches.
 * The report footer section's Height property is set to 0 inches.
 * The report detail section's Height property is set to 9 inches.
 * In the Print Setup dialog box, the Paper Size is set to 8.5 inches by 11 inches.
 * In the Print Setup dialog box, the Left, Right, Top, and Bottom margins are each set to 1 inch.

To create a report based on a Recordset object, follow these steps:

 Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).  Create a new report not based on any table or query, and open the report in Design view. Set the following report properties, and then save the report as RecordSet Report:       Report: RecordSet Report --      RecordSource:  Caption: Report Based on Recordset Width: 6.5 inches   On the View menu, click Code. In the report module, type the following lines in the Declarations section, and then close the module: Option Explicit Dim db As Database, rs As Recordset, PageStart As String   Select the report. Set the report's OnOpen property to [Event Procedure]. Click the Build button to the right of [Event Procedure], and then type the following event procedure in the report module: ' To create a Recordset object based on the Customers table. Set db = DBEngine.Workspaces(0).Databases(0) Set rs = db.OpenRecordset("Customers")   Set the report's OnClose property to [Event Procedure]. Click the Build button to the right of [Event Procedure], and then type the following event procedure in the report module: ' To close the recordset when the report has been printed. rs.Close db.Close  On the Format menu, click Report Header/Footer.</li> Select the report header section and set its Height property to 0.</li> Select the report footer section and set its Height property to 0.</li>  Select the report header section. Set the report header section's OnFormat property to [Event Procedure]. Click the Build button to the right of [Event Procedure], and then type the following event procedure in the report module: ' To reset the pointer to the first record in the recordset. rs.MoveFirst ' To set the unit of measure, font, and font size used in the ' report. Me.scalemode = 1' twips Me.fontname = "Arial" Me.FontSize = 12 </li> Select the report detail section and set its Height property to 9 inches.</li>  Set the report detail section's OnFormat property to [Event Procedure]. Click the Build button to the right of [Event Procedure], and then type the following event procedure in the report module: ' 1. Determines how many pages are needed for the report and '   sets the NextRecord and MoveLayout properties accordingly. ' 2. To save the bookmark to the first record that is printed '   on the current page when the FormatCount property is an odd '   number.

Dim i As Integer If FormatCount Mod 2 = 1 Then If Not rs.eof Then PageStart = rs.Bookmark i = 0 Do While Not rs.eof And i < 18 i = i + 1 rs.MoveNext Loop End If  End If Me.nextrecord = rs.eof Me.movelayout = Not rs.eof </li>  Set the report detail section's OnPrint property to [Event Procedure]. Click the Build button to the right of [Event Procedure], and then type the following event procedure in the report module: ' To return to the first record for that page. Dim i As Integer i = 0 rs.bookmark = PageStart

' To add a border around the entire page. Me.Line (0, 0)-(Me.Width, Me.Section(0).Height),, B

' To print a page's worth of data using ' .5 inches (720 twips) per record. Do While Not rs.eof And i < 18 Me.CurrentY = i * 720 Me.CurrentX = 0 Me.Print rs![CompanyName]; ' or [Company Name] in Microsoft ' Access 2.0 Me.CurrentX = 1440 * 3 Me.Print rs![City]; Me.CurrentX = 1440 * 5 Me.Print rs![Country]; i = i + 1 rs.MoveNext Loop </li> Preview the report. Note that each record in the recordset is displayed in the report.</li></ol>

<div class="references_section">