ACC2000: Subform Not Synchronized with Main Form in Microsoft Access Project (236368)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q236368
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you move from record to record in a main form, the main form and the nested subform are not synchronized if either of the following conditions is true:
  • You are working in a Microsoft Access project that you created by running the Upsizing Wizard. When you upsized the original database, you chose the Create a new client-server application option.

    -or-

  • You manually created or modified the nested subform and set its RecordSource property to the name of a stored procedure.

CAUSE

The record source of the subform is a stored procedure.

RESOLUTION

Use a stored procedure with an input parameter as the record source of the subform, and then use the InputParameters property of the subform to supply information to the stored procedure. The following example uses the sample Access project NorthwindCS.adp to show you how to do this.

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

Creating the Main Form

  1. Open the sample Access project NorthwindCS.adp, which by default is located in the \Program Files\Microsoft Office\Office\Samples folder.
  2. In the Tables list, click Orders.
  3. On the Insert menu, click AutoForm.
  4. Save the form as Orders1, and then close it.

Creating the Stored Procedure and Subform

  1. In the Database window, click Stored Procedures, and then click New.
  2. Replace any text in the Stored Procedure Designer with the following Transact-SQL statements:
    Create Procedure "MyRecordSource"
@OrderId int
AS
SELECT * FROM "ORDER DETAILS" WHERE OrderId = @OrderId
  3. Save the stored procedure, and then close it.
  4. In the Stored Procedure list, click MyRecordSource.
  5. On the Insert menu, click AutoForm.
  6. Open the form that the AutoForm Wizard created in Design view, and set the DefaultView and ViewsAllowed properties of the form to Datasheet.
  7. Set the InputParameters property as follows:

    @OrderId int = Forms!Orders1!OrderId

  8. Save the form as MyRecordSource, and then close it.

Adding the Subform to the Main Form

  1. Open the Orders1 form in Design view, and click the Subform/Subreport control in the toolbox.
  2. Place a subform in the details section of the Orders1 form. When the Subform Wizard opens, click the Use an existing form option, and then click the MyRecordSource form.
  3. Save your work, and then open the Orders1 form in Form view.
  4. Open the Orders1 form and note that the main form and the subform are synchronized as you move from record to record in the main form.

MORE INFORMATION

In an Access project, information displayed in a subform is filtered by using a SELECT statement with a WHERE clause. The WHERE clause uses information in the LinkChildFields and LinkMasterFields properties of the subform to filter the data displayed in the subform, depending on values displayed in the main form.

You can use the SELECT statement and the WHERE clause with tables and views, but not with stored procedures. Therefore, values in the LinkChildFields and LinkMasterFields properties are ignored if a stored procedure is the record source of a subform.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

The steps listed here create the same form and subform described in the "Resolution" section. To reproduce the behavior described in the "Symptoms" section, modify the steps in the "Resolution" section as follows:
  • Open the MyRecordSource form in Design view and remove the Input Parameters property setting.
  • Open the MyRecordSource stored procedure and modify it as follows:
    ALTER Procedure "MyRecordSource"
AS
SELECT * FROM "ORDER DETAILS"
  • Open the Orders1 form and set the LinkChildFields and LinkMasterFields properties of the subform control to OrderId.
You can also create the forms from scratch by following these steps.

Creating the Main Form

  1. Open the sample Access project NorthwindCS.adp, which by default is located in the \Program Files\Microsoft Office\Office\Samples folder.
  2. In the Tables list, click Orders.
  3. On the Insert menu, click AutoForm.
  4. Save the form as Orders1, and then close it.

Creating the Stored Procedure and Subform

  1. In the Database window, click Stored Procedures, and then click New.
  2. Replace any text in the Stored Procedure Designer with the following Transact-SQL statements:
    Create Procedure "MyRecordSource"
AS
SELECT * FROM "ORDER DETAILS"
  3. Save the stored procedure, and then close it.
  4. In the Stored Procedure list, click MyRecordSource.
  5. On the Insert menu, click AutoForm.
  6. Open the form that the AutoForm Wizard created in Design view, and set the DefaultView and ViewsAllowed properties to Datasheet.
  7. Save the form as MyRecordSource, and then close it.

Adding the Subform to the Main Form

  1. Open the Orders1 form in Design view, and click the Subform/Subreport control in the toolbox.
  2. Place a subform in the details section of the Orders1. When the Subform Wizard opens, click the Use an existing form option, and then click the MyRecordSource form.
  3. Set the LinkChildFields and LinkMasterFields properties of the subform control to OrderId.
  4. Save your work, and then open the Orders1 form in Form view.
  5. Use the record navigation buttons on the Orders1 form, and note that as you move from record to record, data in the subform does not change.

REFERENCES

For more information about main forms and subforms, click Microsoft Access Help on the Help menu, type subforms in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about stored procedures and input parameters, refer to SQL Server 7.0 Books Online, which is available for download from the following Microsoft Web site:

http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe

Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbdownload KbClientServer kbprb KB236368
©2004 Microsoft Corporation. All rights reserved.