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

MORE INFORMATION

The following example calls a Visual Basic for Applications function in a report's OnFormat property 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:

1. Open the sample database Northwind.mdb.
2. Use the Report Wizard to create a new report based on the Current Product List query with the following options:
   
   
   1. Include both fields
   2. Add no grouping levels
   3. Sort by ProductName
   4. Use the default Layout setting
   5. Select Corporate style
   6. Name the report Fill Report
3. Open the Fill Report report in Design view.
4. Add the following text box to the Fill Report report's detail section:

   Name: Quantity
   ControlSource: <leave empty>

5. Type the following sample code in a new or existing module:

         Function FillRep()
         Dim rs As Recordset
         Dim db As 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 report detail section's OnFormat property 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.