ACC: How to Base a Report on a Recordset Object

• Microsoft Access versions 2.0, 7.0

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.

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.

REFERENCES

For more information about the NextRecord property, search for "NextRecord," and then "NextRecord property" using the Microsoft Access for Windows 95 Help Index.

Microsoft Access "Building Applications with Microsoft Access for Windows 95," version 7.0, Chapter 9, "Working with Sets of Records," pages 217-246