ACCESS REPORTS USING VISUAL BASIC
Gualter Costa
please send me a e-mail with code examples to :
Tnxs
VB-Joker
http://www.videosoft.com/.
--
Regards
VB-Joker
Carsten Thomsen/MCSE
ICQ 40719070
PLEASE post ALL replies to the newsgroup(s) so we can all benefit from the
discussion!
Gualter Costa <gualte...@hotmail.com> wrote in message
news:7n99vm$8j0$1...@fenix.maxitel.pt...
doka
Reports.
http://www.microsoft.com/officedev/articles/Opg/003/003.htm#dex5
Here's the section on the Report Object. Notice in the "Sub
PrintReport()" routine that shows how. Be sure to have a reference to
the Access application object.
Steven Doka
>-----------------------------------------------------------------------------------------------------------------------------<
The Report Object and the Reports Collection
The Report object represents a Microsoft Access report that is open in
Design view, Print Preview, or Layout Preview. Report objects are
grouped in the Reports collection, which is a member of the Microsoft
Access Application object. The Reports collection contains only the
reports that are currently open in the database.
The following table shows the relationship between the Report object and
the Reports collection and other objects and collections in the
Microsoft Access object hierarchy.
Object or collection Is contained by Contains
Report object Reports collection Controls collection
Properties collection
Module object
Reports collection Application object Report objects
Report objects and Form objects have similar characteristics. This
section only summarizes the characteristics of the Report object,
because the same characteristics have been described in detail in the
previous section, "The Form Object and the Forms Collection." For a list
of the properties, methods, and events of the Report object, search
Microsoft Access Help for "Report object," or view the members of the
Report object in the Object Browser.
Referring to Report Objects
To work with a Report object in Visual Basic, you need to refer to the
Report object in the Reports collection. To refer to a report, you must
make sure that the report is open. To open a report with Visual Basic,
use the OpenReport method of the DoCmd object.
You can refer to a Report object and assign it to an object variable in
one of the following ways:
Dim rpt As Report
Set rpt = Reports!Invoice ' Returns a reference to the Invoice report.
Set rpt = Reports("Invoice") ' Returns a reference to the Invoice
report.
Set rpt = Reports(0) ' Returns a reference to the first report in
' the collection.
Report Modules
Like a Form object, a Report object can have an associated module that
is a class module. This module doesn't exist until you create it. You
can create a report module by clicking Code on the View menu while the
report is open in Design view, by setting the report's HasModule
property to True, or by referring to the report's Module property in
Visual Basic.
Creating Reports at Run Time
To create a new report at run time, use the CreateReport function. To
add controls to or delete controls from a report at run time, use the
CreateReportControl function or the DeleteReportControl statement.
The following example uses Automation from Microsoft Excel to create a
linked table in a Microsoft Access database, and then creates a
Microsoft Access report based on the data in the linked table. To use
this example, you need to create a Microsoft Excel workbook named
Revenue.xls, add some data to a worksheet in that workbook, and create a
named range called DataRange that includes this data. Then, enter the
following code in a module in the Microsoft Excel workbook. Before you
run this example, you must set a reference to the Microsoft Access 8.0
object library and the DAO 3.5 object library from Microsoft Excel.
Important Before you run this code, make sure that the Microsoft Excel
ISAM driver (Msexcl35.dll) is installed on your system. If it's not, you
need to run Setup again to install it. The Microsoft Excel ISAM driver
enables Microsoft Excel 97 files to work with the Microsoft Jet database
engine. For more information on working with the Microsoft Excel ISAM
driver, search Microsoft Access Help for "Microsoft Excel driver."
' Enter in Declarations section of a module.
Dim appAccess As New Access.Application
Sub PrintReport()
Dim rpt As Access.Report, ctl As Access.TextBox
Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim strDB As String, intLeft As Integer
' Set this constant to the path to your Northwind sample database.
Const conPath As String = "C:\Program Files\Microsoft
Office\Office\Samples\"
' Open database in Microsoft Access, specifying full path name.
appAccess.OpenCurrentDatabase conPath & "Northwind.mdb"
' Return reference to current database.
Set dbs = appAccess.CurrentDb
' Create new TableDef object.
Set tdf = dbs.CreateTableDef("XLData")
' Specify connection string for Microsoft Excel ISAM driver.
tdf.Connect = "EXCEL 8.0; Database=C:\My Documents\Revenue.xls"
' Specify source table as a named range in a worksheet.
tdf.SourceTableName = "DataRange"
' Append new linked table to database.
dbs.TableDefs.Append tdf
' Create new report in Microsoft Access.
Set rpt = appAccess.CreateReport
' Specify linked table as report's record source.
rpt.RecordSource = tdf.Name
' Create control on report for each field in linked table.
For Each fld In tdf.fields
Set ctl = appAccess.CreateReportControl(rpt.Name, acTextBox, , , _
fld.Name, intLeft)
intLeft = intLeft + ctl.Width
Next fld
' Open report in Print Preview.
appAccess.DoCmd.OpenReport rpt.Name, acViewPreview '---Opens Report
' Restore report.
appAccess.DoCmd.Restore
' Display Microsoft Access as active application.
AppActivate "Microsoft Access"
End Sub
>-----------------------------------------------------------------------------------------------------------------------------<