ACC: How to Generate and Print a Report Without Saving It

ID: Q188859

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

You may have occasion to produce a report, which afterwards you don't want to save. Examples include reporting on a user's responses from a custom wizard or a report on database information, such as record sources that may cause problems with security settings. The reason you would want to delete the report rather than to save it is because you would add to the size of the database file with each save action. This article shows you how to create such a report.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The sample code below does the following:

It creates and populates a simple array.

It creates a simple report and creates event procedures.

It prints the array to the report.

It discards the report without saving it.

For the sake of simplicity, the data in this example will be supplied from a single dimension array and the output report will have just one data field.

Create a new database.

Create a module and type the following lines in the Declarations section if they are not already there:


      Option Compare Database
      Option Explicit
      Public arySampleData() As Variant
      Public strReportName As String

Type the following procedure:


      Public Sub FillArray()
         Dim aryItem As Long
         For aryItem = 0 To 9
            ReDim Preserve arySampleData(aryItem)
            arySampleData(aryItem) = "Item " & aryItem
         Next aryItem

      End Sub

Type the following procedure:


    Sub TempReport()
      Dim rpt As Report
      Dim mdl As Module
      Dim strCode As String
      Dim lngReturn As Long
      Dim ctlLabel1 As Control, ctlText1 As Control
      Dim intDataX As Integer, intDataY As Integer
      Dim intLineCount As Integer, intCounter As Integer

      ' Turn off the screen updating. Comment this line out during
      ' testing because it prevents user interaction if the code fails.
      Application.Echo False

      ' Create new report and store the name.
      Set rpt = CreateReport
      strReportName = rpt.Name

      ' Expose the report header.
      DoCmd.RunCommand acCmdReportHdrFtr
      Set mdl = rpt.Module

      intLineCount = mdl.CountOfLines
      strCode = "Dim intCounter as Integer"
      mdl.InsertLines intLineCount, strCode

      lngReturn = mdl.CreateEventProc("Print", rpt.Section(acHeader).Name)
      strCode = "intCounter=0" & vbCrLf & "FillArray"
      mdl.InsertLines lngReturn + 1, strCode

      lngReturn = mdl.CreateEventProc("Print", rpt.Section(acDetail).Name)
      strCode = "Me!txtTest.Value= arySampleData(intCounter)" & vbCrLf
      strCode = strCode & "Me!lblTest.Caption= ""Array Value""" & vbCrLf
      strCode = strCode & "If intCounter <> UBound(arySampleData)" _
         & "Then Me.nextrecord=False" & vbCrLf
      strCode = strCode & "intCounter=intCounter+1"
      mdl.InsertLines lngReturn + 1, strCode

      ' Set positioning values for report controls.
      ' Measurements are in twips.
      intDataX = 500
      intDataY = 50

      ' Create unbound default-size label and text box in detail section.
      rpt.Section(acDetail).Height = 400
      Set ctlLabel1 = CreateReportControl(reportname:=strReportName, _
         ControlType:=acLabel, Left:=intDataX, Top:=intDataY, _
         Width:=1440, Height:=240)
      ctlLabel1.Name = "lblTest"
      Set ctlText1 = CreateReportControl(reportname:=strReportName, _
         ControlType:=acTextBox, Left:=(intDataX + 1440), Top:=intDataY)
      ctlText1.Name = "txtTest"

      ' Turn the screen back on.
      Application.Echo True

    End Sub

Type the following function:


    Function BuildReport()
      ' Call the TempReport procedure.
      TempReport

      ' Print the report without showing it in preview mode.
      DoCmd.OpenReport strReportName, acViewNormal

      ' Close the report without saving it.
      DoCmd.Close acReport, strReportName, acSaveNo

    End Function

To test this function, type the following line in the Debug window, and then press ENTER:
```
?BuildReport() 
```
Note that the report is printed, and then the report no longer exists.

Note that the variable, intCounter, is initialized and the function, FillArray is called from the Print event of the ReportHeader section rather than from the Open event of the Report, which would cause the example to fail.

REFERENCES

For more information on arrays, see the Help topic "Arrays, using." For more information on "recordsets," see the Help topic "Recordsets, defined." For the "NextRecord property," see the Help topic, "NextRecord Property."

Additional query words:

Keywords : kbdta AccCon RptOthr RptEvent KbVBA
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto