ACC: How to Use Visual Basic to Fill Text Boxes on a Report

ID: Q143280

The information in this article applies to:

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a sample Visual Basic for Applications function that populates (or fills) controls on a report based on a table or query when you preview or print the report. The sample function fills a newly-created text box on a report with a value from the QuantityPerUnit field in the Products table of the sample database Northwind.mdb. The report is based on the Current Product List query of the Northwind database.

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 your version of the "Building Applications with Microsoft Access" manual.

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

The following example calls a Visual Basic for Applications function in a report's OnFormat property to fill an unbound control on the report. As an alternative, you can call the function from the ControlSource property of the unbound control. To create the example, follow these steps:

1. Open the sample database Northwind.mdb.

2. Use the Report Wizard to create a new report based on the Current

   Product List query with the following options:

    - Include both fields
    - Add no grouping levels
    - Sort by ProductName
    - Use the default Layout setting
    - Select Corporate style
    - Name the report Fill Report

4. Add the following text box to the Fill Report report's detail section:

      Name: Quantity
      ControlSource: <leave empty>

      Function FillRep()
      Dim rs As Recordset
      Dim db As DATABASE

      Set db = CurrentDb()
      Set rs = db.OpenRecordset("Products", dbOpenDynaset)

      rs.MoveFirst
      rs.FindFirst "[ProductID]=" & Reports![Fill Report]![ProductID]

      ' Use this line if the function is called from report
      ' detail section's OnFormat property event procedure.
      Reports![Fill Report]![Quantity] = rs![QuantityPerUnit]

      ' -or- use this line if the function is called from the
      ' ControlSource property of the unbound control.
      FillRep = rs![QuantityPerUnit]
      End Function

      =FillRep()

   NOTE: You can also call the FillRep() function from the ControlSource
   property of the unbound Quantity text box as follows:

      Name: Quantity
      ControlSource: =FillRep()

   If you use this method, add a comment (') in front of this line of code
   in step 5:

      Reports![Fill Report]![Quantity] = rs![QuantityPerUnit]

   filled by the Visual Basic for Applications function.

REFERENCES

For an example of how to fill text boxes on a report using Access Basic, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q109943
   TITLE     : How to Fill Text Boxes on a Report Using Access Basic

Keywords          : kbprg RptOthr 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998