ACC2000: Error with Subform Based on Hierarchical ADO Recordset

MORE INFORMATION

Microsoft Access 2000 exposes a new Recordset property for forms through which you can assign a recordset to a form. ActiveX Data Objects (ADO) allows you to create hierarchical, or chaptered, recordsets where each row in a recordset contains a recordset with related child records (such as the subdatasheets in tables).

Steps to Reproduce Problem

1. Create a new database in Access called TestDB.mdb.
2. On the File menu, point to Get External Data, and then click Link Tables.
3. Browse to the sample database Northwind.mdb, select it, and click Link.
4. In the Link Tables dialog box, select the Categories and Products tables. Click OK.
5. In TestDB.mdb, click Forms under Objects, and then click Create form by using Wizard.
6. Select the Categories table and include all the fields. Click Finish.
7. View the new Categories form in Design view.
8. Click the Subform/Subreport button in the toolbox and draw a subform on the Categories form.
   
   NOTE: In order to draw the subform, you may need to resize the Categories form.
9. In the SubForm Wizard, click Next on the first screen.
10. On the second screen, select the Products table and include all fields. Click Finish.
11. Add the following code to the Open event of the Categories main form.
    
    NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.
    
    NOTE: The following code assumes the Northwind sample database is installed in its default location on drive C. If this is different on your installation, please change the path accordingly.

    Sub Form_Open(Cancel As Integer)
    
       Dim cn As New ADODB.Connection
       Dim rsCategories As New ADODB.Recordset
       Dim rsProducts As ADODB.Recordset
       Dim strSQL As String
    
       cn.Provider = "MSDataShape"
          
       cn.Open "DATA PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE = " & _ 
       "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
    
       strSQL = "SHAPE {SELECT * FROM Categories} APPEND " & _ 
       "({SELECT * FROM Products} As rsProducts RELATE CategoryID TO " & _
       "CategoryID)"
    
       rsCategories.Open strSQL, cn, adOpenKeyset, adLockOptimistic
    
       Set Me.Recordset = rsCategories
       Set rsProducts = rsCategories("rsProducts").Value
       Set Me![Products Subform].Form.Recordset = rsProducts
      
    End Sub
    					

12. Save the form and close it.
13. Open the form in Form view. Note that you receive the following error:
    Run-time error '2147417848(80010108)':
    Method 'Recordset' of object '_Form_Products subform' failed.