How to use Visual Basic to fill text boxes on a report in Microsoft Access

This article shows you how to create a sample Visual Basic for Applications function that populates (or fills) controls on a report that is based on a table or a 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.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For additional information about how to obtain this sample file, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following example calls a Visual Basic for Applications function in the OnFormat property of a report 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.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. Use the Report Wizard to create a new report that is 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.
   
   
3. Open the Fill Report report in Design view.
4. Add the following text box to the detail section of the Fill Report report:

      Text box
      ----------------------------
      Name: Quantity
      ControlSource: <leave empty>
   					

5. Type the following sample code in a new or existing module.NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
   
   

   Function FillRep()
   Dim rs As DAO.Recordset
   Dim db As DAO.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
   
   					

6. Set the OnFormat property of the detail section of the report as follows:
   =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]
   
   					

7. Preview the report. Note that the Quantity text box on the report is filled by the Visual Basic for Applications function.