ACC2002: Can't Expand a Table's Subdatasheet (287476)


The information in this article applies to:

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

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

SYMPTOMS

You are unable to expand subdatasheets in a table's datasheet view when you use a function as the source object for the subdatasheet.

CAUSE

This behavior can occur because functions cannot be used as source objects for subdatasheets in tables.

RESOLUTION

To work around this issue, use either of the following methods.

Method 1: Use a View as the Subdatasheet's Source Object

To do this, follow these steps:
  1. Create a view that selects the same data as the function that you have been using as the source object for your primary table's subdatasheet.
  2. Open your primary table in Design view and display the property sheet.
  3. In the Subdatasheet Name box on the Data tab, select the view that you created in step 1.

Method 2: Use a Form and Subform in Datasheet View

To do this, follow these steps:
  1. Create a new form based on the function that selects your subdatasheet data, make its default view Datasheet view, and then save it as FormSub.
  2. Create a second new form based on your primary table, make its default view Datasheet view, and then save it as FormMain.
  3. Open FormMain in Design view.
  4. In the Database window, select the form FormSub, and then drag it onto the Detail section of the form FormMain.

    NOTE: You must drag the subform into position in this step; using the Subform/Subreport wizard does not work in this case.
  5. Display the property sheet, and then set the Link Child and Link Master fields appropriately.
  6. Save the form, and then open it in Datasheet view.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

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.

  1. Start Access, and then open the sample project NorthwindCS.adp.
  2. Under Objects, click Queries, and then double-click Create function in designer.
  3. In the Add Tables dialog box, select Orders, and then click Add. Click Close.
  4. In the Orders field list, click OrderID, and then click CustomerID. Click OrderDate.
  5. On the toolbar, click Save, type fn_CustOrders in the Function name box, and then click OK.
  6. Under Objects, click Tables, and then click the Customers table. On the Edit menu, click Copy, and then click Paste.
  7. In the Table Name box, type CustTest, and then click OK.
  8. Click CustTest, click Design, and then click Properties on the Table Design toolbar.
  9. On the Data tab, click the Subdatasheet Name box, and then select Function: fn_CustOrders from the list.
  10. In the Link Child Fields box, type CustomerID.
  11. In the Link Master Fields box, type CustomerID.
  12. Save the table CustTest, and then open it in Datasheet view.
  13. Click to expand any record.

    The subdatasheet does not appear.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kbnofix KB287476
©2003 Microsoft Corporation. All rights reserved.