This article was previously published under Q291533
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
Moderate: Requires basic macro, coding, and interoperability skills.
For a Microsoft Access 2000 version of this article, see 210136.
For a Microsoft Access 97 version of this article, see 143280.
This article shows you how to create a sample Visual Basic for Applicationsfunction 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 functionfills a newly created text box on a report with a value from theQuantityPerUnit field in the Products table of the sample databaseNorthwind.mdb. The report is based on the Current Product List queryof the Northwind database.
NOTE: This article explains a technique demonstrated in the samplefile, 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:
231851 ACC2000: Microsoft Access 2000 sample reports available in Download Center
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.
Open the sample database Northwind.mdb.
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.
Open the Fill Report report in Design view.
Add the following text box to the detail section of the Fill Report report:
Text box ---------------------------- Name: Quantity ControlSource: <leave empty>
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.RecordsetDim db As DAO.DATABASESet db = CurrentDb()Set rs = db.OpenRecordset("Products", dbOpenDynaset)rs.MoveFirstrs.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
Set the OnFormat property of the detail section of the report as follows:
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 codein step 5: