ACC: How to Fill Text Boxes on a Report Using Access Basic (109943)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
This article was previously published under Q109943
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article describes two methods that you can use to populate (or fill) controls on a report based on a table or query when you run the report.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic," in version 2.0.

MORE INFORMATION

Both of the examples below use Access Basic code to fill a control on a report based on the Employee List query. The code will fill in the City field on a report that only contains the Employee ID and Employee Name fields.

The first example demonstrates how to fill a field using Access Basic code called from the OnFormat property of the report's detail section:
  1. Open the sample database NWIND.MDB.
  2. Using the Report Wizard, create a new report based on the Employee List query with the following options: 
        - Single-Column
    - Include both fields
    - Sort by Employee Name
    - Select Executive style
  3. View the report in Design view.
  4. Add the following text box to the report's detail section: 
          ControlName: City
      ControlSource: <leave empty>
  5. Save the report as Fill Report1.
  6. Type the following sample code in a new or existing module: 
          Option Explicit

      Function fillrep()
         Dim ds as Dynaset, db as Database
         Set db=CurrentDB()
         set ds=db.CreateDynaset("Employees")
         ds.MoveFirst
         ds.FindFirst "[Employee ID]=" & Reports![Fill Report1]![Employee _
                ID]
         Reports![Fill Report1]![City]=ds![city]
      End Function
  7. Set the report detail section's OnFormat property as follows: 
          =fillrep()
  8. Preview the report. Note that the City field on the report is filled by the Access Basic code.
The second example demonstrates how to fill a field using Access Basic code called from a text box's ControlSource property:
  1. Open the sample database NWIND.MDB.
  2. Using the Report Wizard, create a new report based on the Employee List query with the following options: 
        - Single-Column
    - Include both fields
    - Sort by Employee Name
    - Select Executive style
  3. View the report in Design view.
  4. Add the following text box to the report's detail section: 
          ControlName: City
      ControlSource: <leave empty>
  5. Save the report as Fill Report2.
  6. Enter the following sample code in a new or existing module: 
          Function fillit()
         Dim ds as Dynaset, db as Database
         Set db=CurrentDB()
         set ds=db.CreateDynaset("Employees")
         ds.MoveFirst
         ds.FindFirst "[Employee ID]=" & Reports![Fill Report2]![Employee _
              ID]
         fillit=ds!city
      End Function
  7. Change the City text box's ControlSource property as follows: 
          =fillit()
  8. Preview the report. Note that the City field on the report is filled by the Access Basic code.

REFERENCES

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

143280 ACC: How to Fill Text Boxes on a Report Using Visual Basic
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbhowto kbusage KB109943
©2002 Microsoft Corporation. All rights reserved.