Microsoft KB Archive/109943

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

Article ID: 109943

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



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:

 Open the sample database NWIND.MDB.  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  View the report in Design view.  Add the following text box to the report's detail section:

     ControlName: City ControlSource:  Save the report as Fill Report1.</li>  Type the following sample code in a new or existing module:

<pre class="fixed_text">     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 </li>  Set the report detail section's OnFormat property as follows:

<pre class="fixed_text">     =fillrep </li> Preview the report. Note that the City field on the report is filled by the Access Basic code.</li></ol>

The second example demonstrates how to fill a field using Access Basic code called from a text box's ControlSource property:

<ol> Open the sample database NWIND.MDB.</li>  Using the Report Wizard, create a new report based on the Employee List query with the following options:

<pre class="fixed_text">   - Single-Column - Include both fields - Sort by Employee Name - Select Executive style </li> View the report in Design view.</li>  Add the following text box to the report's detail section:

<pre class="fixed_text">     ControlName: City ControlSource: </li> Save the report as Fill Report2.</li>  Enter the following sample code in a new or existing module:

<pre class="fixed_text">     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 </li>  Change the City text box's ControlSource property as follows:

<pre class="fixed_text">     =fillit </li> Preview the report. Note that the City field on the report is filled by the Access Basic code.</li></ol>

<div class="references_section">