Microsoft Office 2000/Visual Basic Programmer's Guide

Creating Access Reports

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