Microsoft KB Archive/824641

= No data appears in a subreport when the subreport is linked by an AutoNumber field with the Field Size property set to Replication ID =

Article ID: 824641

Article Last Modified on 8/29/2006

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-





SYMPTOMS
In a Microsoft Access database, if you view a report in the Print Preview mode that is linked to a subreport by using an AutoNumber field, and if the Field Size property of the field is set to Replication ID, the subreport may not display any data.



CAUSE
The Datasheet view of a table or a query that contains a field of data type AutoNumber (with its FieldSize property set to Replication ID) returns or displays the Replication ID as a string, instead of returning or displaying the Replication ID as a GUID.



WORKAROUND
To work around the problem, use one of the following methods:  === Method 1: Use a Subform Instead of a Subreport ===

Create a subform where the subform is based on the same recordset as that of the existing subreport, and then link the created subform instead of the subreport to the main report. To do this, follow these steps:  Add a form that is based on the same recordset as the subreport. Open the main report in the Design view. Insert a Subform/Subreport control to the report from the Toolbox. In the SubReport Wizard, click to select Use an existing report or form, and then click the name of the form that was created in step1 from the list box. Click Next.</li> Under Form/report fields, select the AutoNumber field in the main report.</li> Under Subform/subreport fields, select the corresponding field in the subform that was created in step 1.</li> Click Finish. Notice that the data in the subform appears when you view the main report in Print Preview.</li></ol> </li> === Method 2: Modify the Record Source of the Reports ===

You can modify the record source of the main report and the subreport to use queries so that the corresponding query returns all the columns from the relevant table. Additionally, the queries must use the CStr function to convert the contents of the Replication ID field to a string value. To do this, follow these steps: <ol>  Create a query for the record source of the main report.

For example, assume that the source of the main report is a table that is named Project. The Project table contains two fields, PID and PName. In this example, PID is an AutoNumber field with the Field Size property set to Replication ID. Create a query that is named Query1 that is similar to the following query: SELECT CStr([PID]) AS strPID, PName FROM Project </li>  Create a query for the record source of the subreport.

For example, assume that the source of the subreport is a table that is named Tasks. The Tasks table contains two fields, TaskID and RelatedProject. In this example, RelatedProject is a Number field with the Field Size property set to Replication ID that references the PID column of the Project table. Create a query that is named Query2 that is similar to the following query: SELECT TaskID, CStr([RelatedProject]) AS strRelatedProject FROM Project </li> Create the main report that is based on the Query1 query.</li> Open the main report in Design view.</li> Insert a Subform/Subreport control in the report from the Toolbox.</li> In the SubReport Wizard, click to select Use existing Tables and Queries, and then click Next.</li> Click Query: Query2 in the Tables/Queries list.</li> Click >> to move all fields from the Available Fields list to the Selected Fields list, and then click Next.</li> Under Form/report fields, select strPID.</li> Under Subform/subreport fields, select strRelatedProject.</li> Click Finish. Notice that the data in the subreport also appears when you view the main report in Print Preview.</li></ol> </li></ul>

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> Start Microsoft Access.</li> <li>Create a new database that is named Db1.mdb.</li> <li> Create a table that is Project with the following specifications:

<pre class="fixed_text">Table: Project -- PID AutoNumber PName  Text

Column: PID -- Field Size Replication ID    </li> <li> Create another table that is named Tasks with the following specifications:

<pre class="fixed_text">Table: Tasks -- TaskID     AutoNumber RelatedProject Number

Column: TaskID -- Field Size Replication ID

Column: RelatedProject -- Field Size Replication ID

</li> <li> Create and then run the following query to insert data in the Project table and the Tasks table: INSERT INTO project ( PName ) VALUES ('test'); and INSERT INTO tasks ( RelatedProject ) SELECT PID FROM project; </li> <li>In the Database window, click Reports under Objects.</li> <li>In the right pane, double-click Create report by using wizard.</li> <li>Click Table: Project in the Tables/Queries list.</li> <li>Click >> to move all fields from the Available Fields list to the Selected Fields list, and then click Finish.

Note Notice that the Project report is automatically opened in Print Preview.</li> <li>On the View menu, click Design View.</li> <li>Insert a Subform/Subreport control in the report from the Toolbox.</li> <li>In the SubReport Wizard, click to select Use existing Tables and Queries, and then click Next.</li> <li>Click Table: Tasks in the Tables/Queries list.</li> <li>Click >> to move all fields from the Available Fields list to the Selected Fields list, and then click Next.</li> <li>Under Form/report fields, select PID.</li> <li>Under Subform/subreport fields, select RelatedProject, and then click Finish.</li> <li>On the File menu, click Save.</li> <li>On the View menu, click Print Preview. Notice that the data that corresponds to the subreport is missing.</li></ol>

<div class="references_section">