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

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.

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.

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:
  1. Add a form that is based on the same recordset as the subreport.
  2. Open the main report in the Design view.
  3. Insert a Subform/Subreport control to the report from the Toolbox.
  4. 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.
  5. Click Next.
  6. Under Form/report fields, select the AutoNumber field in the main report.
  7. Under Subform/subreport fields, select the corresponding field in the subform that was created in step 1.
  8. Click Finish. Notice that the data in the subform appears when you view the main report in Print Preview.

• 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:
  1. 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

  2. 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

  3. Create the main report that is based on the Query1 query.
  4. Open the main report in Design view.
  5. Insert a Subform/Subreport control in the report from the Toolbox.
  6. In the SubReport Wizard, click to select Use existing Tables and Queries, and then click Next.
  7. Click Query: Query2 in the Tables/Queries list.
  8. Click >> to move all fields from the Available Fields list to the Selected Fields list, and then click Next.
  9. Under Form/report fields, select strPID.
  10. Under Subform/subreport fields, select strRelatedProject.
  11. Click Finish. Notice that the data in the subreport also appears when you view the main report in Print Preview.

Steps to Reproduce the Behavior

1. Start Microsoft Access.
2. Create a new database that is named Db1.mdb.
3. Create a table that is Project with the following specifications:
   
   

   Table: Project
   ------------------------------
   PID	AutoNumber
   PName	Text
   
   Column: PID
   ------------------------------
   Field Size	Replication ID
   	

4. Create another table that is named Tasks with the following specifications:
   
   

   Table: Tasks
   ------------------------------
   TaskID		AutoNumber
   RelatedProject	Number
   
   
   Column: TaskID
   ------------------------------
   Field Size	Replication ID
   
   
   Column: RelatedProject
   ------------------------------
   Field Size	Replication ID
   
   	
   
   	

5. 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;
   

6. In the Database window, click Reports under Objects.
7. In the right pane, double-click Create report by using wizard.
8. Click Table: Project in the Tables/Queries list.
9. 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.
10. On the View menu, click Design View.
11. Insert a Subform/Subreport control in the report from the Toolbox.
12. In the SubReport Wizard, click to select Use existing Tables and Queries, and then click Next.
13. Click Table: Tasks in the Tables/Queries list.
14. Click >> to move all fields from the Available Fields list to the Selected Fields list, and then click Next.
15. Under Form/report fields, select PID.
16. Under Subform/subreport fields, select RelatedProject, and then click Finish.
17. On the File menu, click Save.
18. On the View menu, click Print Preview. Notice that the data that corresponds to the subreport is missing.

For more information about the AutoNumber data type and Replication ID, click Microsoft Office Access Help on the Help menu, type about autonumber field size and replicated databases in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For more information, click the following article number to view the article in the Microsoft Knowledge Base: