ACC2000: Error with Subform Based on Hierarchical ADO Recordset

• Microsoft Access 2000

SYMPTOMS

When you use the MSDatashape provider to create a hierarchical recordset, and you then assign the recordset to a subform, you may receive the following error message:

Run-time error '2147417848(80010108)':
Method 'Recordset' of object '_Form_<form name>' failed

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

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 need to reference the Microsoft ActiveX Data Objects 2.1 Library.
    
    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.

Additional query words: pra chapter shape

Keywords : kberrmsg kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug