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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000

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

MORE INFORMATION

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.

REFERENCES

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:

210612 Parameter query returns no records with Replication ID field

Modification Type:MinorLast Reviewed:8/29/2006
Keywords:kbDatabase kbReport kbprb KB824641 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.