| The following KB article shows how this can be done though you have hard code
the formatting. You might be able to work around this by using unbound text
boxes instead.
You can pass the Recordset object to the report via a global variable defined
in a module:
Public globRecordset as Recordset
Hope this helps.
Dennis.
===============
PSS ID Number: Q132881
Article last modified on 11-05-1996
PSS database name: ACCESS
2.00 7.00
WINDOWS
---------------------------------------------------------------------
The information in this article applies to:
- Microsoft Access version 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.
To create a report based on a Recordset object, follow these steps:
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![Company Name];
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
Additional reference words: 2.00 dynaset unbound prevent
KBCategory: kbusage kbhowto
KBSubcategory: RptProp
=============================================================================
Copyright Microsoft Corporation 1996.
[Posted by WWW Notes gateway]
|