HOW TO: Set Subreport's RecordSource Property at Run Time in Access 2000

This article shows you how to ensure that the RecordSource property of a linked subreport is set only the first time that the Open event of the subreport is triggered. A linked subreport triggers the Open event several times, so you must make sure that the RecordSource property is set only once.

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.

You can set the RecordSource property of a subreport from its OnOpen event at run time only. At all other times, this property is read-only and cannot be modified.

To create a report and subreport in which the RecordSource of the subreport is selected at run time, 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.

Create Two Example Queries

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. In the Database window, click Queries and then click New to create a new query based on the Customers table.
3. Add the CustomerName field to the query grid and type Like 'A*' in the Criteria row.
4. Save the query as qryTest-A.
5. Change the criteria to Like 'B*' and save the query with the new name qryTest-B and close the query.

Create a Subreport

1. In the Database window, click Reports and then click New to create a new report.
2. Click the qryTest-A query in the selection list to indicate where the data comes from and then click OK.
3. On the View menu, click Page Header/Footer to remove those sections from the report.
4. Add the CompanyName to the Detail section of the report and shorten the Detail section to remove unnecessary white space.
5. Save the report as rptTestSub and close the report.

Create a Main Report

1. Create a new report in Design view without selecting a record source.
2. Click the Subform/Subreport tool in the Toolbox and then click in the top left corner of the Detail section.
3. Click Use an Existing Report or Form, click rptTestSub in the list box and then click Finish.
4. Save the report as rptTestMain.
5. Click Preview and observe that the customers listed are those whose names begin with "A."
6. Close the report.

Use the Sub-report's OnOpen Event to Change Its RecordSource Property

1. Open rptTestSub in Design view.
2. In the report's property sheet, click the Event tab, and create the following OnOpen event procedure:

   Private Sub Report_Open(Cancel As Integer)
       Static intCallCount As Long
   
       If intCallCount = 0 Then Me.RecordSource = "qryTest-B"
       intCallCount = intCallCount + 1
   End Sub
   					

3. Close the Visual Basic Editor and close and save the rptTestSub report.

Test the Report

Open rptTestMain and notice that the customers listed are now those whose names begin with "B." NOTE: Microsoft Access checks the value of the static variable intCallCount. If intCallCount is zero, this is the first time the Open event is triggered, so the code sets the RecordSource property. Subsequent calls to the Open event will have a value of intCallCount greater than zero.

The variable intCallCount is declared in a Static statement because its value must be retained through several Open events. If it had been declared using the Dim keyword, its value would revert to 0 on each event and the code would fail.

For additional informationAbout Setting Report Properties at Run Time, click the article number below to view the article in the Microsoft Knowledge Base: