ACC: How to Base a Report on a Recordset Object

MORE INFORMATION

• 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.

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. 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: <Leave blank>
          Caption: Report Based on Recordset
          Width: 6.5 inches
   					

3. 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
   					

4. 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")
   					

5. 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
   					

6. On the Format menu, click Report Header/Footer.
7. Select the report header section and set its Height property to 0.
8. Select the report footer section and set its Height property to 0.
9. 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
   					

10. Select the report detail section and set its Height property to 9 inches.
11. 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
    					

12. 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
    					

13. Preview the report. Note that each record in the recordset is displayed in the report.