You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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, 	PNameFROM 	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 strRelatedProjectFROM 	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	AutoNumberPName	TextColumn: PID------------------------------Field Size	Replication ID	
  4. Create another table that is named Tasks with the following specifications:

    Table: Tasks------------------------------TaskID		AutoNumberRelatedProject	NumberColumn: TaskID------------------------------Field Size	Replication IDColumn: 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 PIDFROM 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
ACC2003 ACC2002 ACC2000 data loss missing data
Properties

Article ID: 824641 - Last Review: 08/29/2006 21:30:02 - Revision: 1.5

  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • kbdatabase kbreport kbprb KB824641
Feedback