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

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

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

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.

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: