Creating Access Reports
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
If your data is stored in an Access database and you want to create a report that displays dynamic data, an Access report is a simple solution. The data in an Access report is refreshed each time you reopen the report. You can also format Access reports easily, so they make excellent printed reports. However, an Access report is read-only; users cannot manipulate, analyze, or filter the data in the report. If your solution requires that users be able to manipulate the data in novel ways, consider another type of report.
You can create an Access report from VBA code by using the CreateReport method, and then add controls to the report by using the CreateReportControl method. The following PropertyLet procedure creates an Access report from a table or query.
Private Property Let CustomReport_Source(RHS As String) ' Create report based on specified data source. Dim txtNew As Access.TextBox Dim lblNew As Access.Label Dim rstSource As ADODB.Recordset Dim fldData As ADODB.Field Dim lngTop As Long Dim lngLeft As Long lngLeft = 0 lngTop = 0 ' Set report's RecordSource property. Me.Report.RecordSource = RHS ' Open recordset on specified record source. Set rstSource = New ADODB.Recordset rstSource.Open "SELECT * FROM [" & RHS & "];", _ CurrentProject.Connection, adOpenForwardOnly ' Create corresponding label and text box controls for each field. For Each fldData In rstSource.Fields ' Create new text box control and size to fit data. Set txtNew = CreateReportControl(Me.Report.Name, acTextBox, _ acDetail, , fldData.Name, lngLeft + 1500, lngTop) txtNew.SizeToFit ' Create new label control and size to fit data. Set lblNew = CreateReportControl(Me.Report.Name, acLabel, acDetail, _ txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height) lblNew.SizeToFit ' Increment top value for next control. lngTop = lngTop + txtNew.Height + 25 Next CustomReport_Source_End: On Error Resume Next rstSource.Close Set rstSource = Nothing Exit Property End Property
This procedure is available in the AccessReport class module of the NorthwindReports.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.
For more information about creating and formatting Access reports, search the Microsoft Access Help index for "reports."