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

Last reviewed: April 24, 1997
Article ID: Q109943
The information in this article applies to:
  • 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.

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:

   ARTICLE-ID: Q143280
   TITLE     : ACC: How to Fill Text Boxes on a Report Using Visual Basic
 

	
	


Keywords : kbusage McrArg
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.