Microsoft KB Archive/331917

Knowledge Base

PRB: Changes to the Filter property in a hierarchical recordset appears to be read-only

Article ID: 331917

Article Last Modified on 2/3/2004

---

APPLIES TO

• Microsoft Visual Basic 6.0 Enterprise Edition
• Microsoft Visual Basic 6.0 Professional Edition
• Microsoft ActiveX Data Objects 2.7

---

This article was previously published under Q331917

SYMPTOMS

In a hierarchical recordset, changes that are made to the Filter property on the chapter field are lost. The changes do not affect the original chapter. The property appears as read-only. However, no errors or warnings are produced.

STATUS

This behavior is by design.

RESOLUTION

To resolve this problem, create a variable that is populated by referencing the chapter field. The following example demonstrates the behavior. In the example, a new recordset object variable holds reference to the resultant filtered recordset. The AbsolutePosition property also shows similar behavior.

When you issue the following command

rs.Fields("Orders").Value.Filter = "OrderId = 3"

the following sequence of events occurs:

• rs.Fields("Orders").Value causes Microsoft ActiveX Data Objects (ADO) to open a handle over the child chapter. The Orders value in the rowset that is wrapped by the parent rowset is a chapter handle, and not a rowset or recordset. The child recordset is an ADO construction over the child rowset or the chapter handle, or both.
• .Filter = "OrderId = 3" sets the filter on the newly opened recordset to "OrderId = 3".
• ADO closes the child recordset by removing the reference to the filtered recordset. Therefore, the Filter property is lost.

There were no errors because the Filter property was successfully set, and the recordset was destroyed automatically.

MORE INFORMATION

Steps to reproduce the behavior

1. Create a new Visual Basic project.

2. Add a command button to the form, and then set the following properties.

   Name = cmdFilterChild
   Caption = Filter Child

3. On the Project menu, click References. Add a reference to Microsoft ActiveX Data Objects 2.0 Library.

4. Paste the following code in the code window.

   Private Sub cmdFilterChild_Click()
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim rsChild As ADODB.Recordset
      Dim rs2 As ADODB.Recordset
      
      ' Open a Connection
      Set cn = New ADODB.Connection
      cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"
      
      ' Open the hierarchical recordset
      Set rs = New ADODB.Recordset
      rs.CursorLocation = adUseClient
      rs.Open "SHAPE {SELECT * FROM Employees} AS Employees APPEND ({SELECT * FROM Orders} " & _
         "AS Orders RELATE EmployeeID TO EmployeeID)", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
         
      ' Set a filter on the child records.
      MsgBox "Child filter before setting the filter property : " & _
         rs.Fields("Orders").Value.Filter
      rs.Fields("Orders").Value.Filter = "OrderId = 3"
      
      ' Display the OrderId set in previous step
      MsgBox "You expect the Child filter to be 'OrderId = 3'. However, you receive: OrderId = " & _
         rs.Fields("Orders").Value.Filter & _
             ". This behavior occurs because ADO closes the Recordset because of No Reference."
         
      
      ' This code references this recordset.
      ' The recordset stays open because the user is holding a reference to it.
      Set rs2 = rs.Fields("Orders").Value
      rs2.Filter = "OrderId = 3"
         
      MsgBox "Child filter is now 'OrderId = 3' and it persists: " & rs2.Filter & _
         ". This behavior occurs because ADO has Reference."
   End Sub

5. Modify the Data Source connection string to correctly reflect the path of your NWIND.mdb.
6. Save, and then run the sample code. Notice that a message box appears that indicates the progress.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

230167 BUG:DataCombo/DataList Not Displaying Recordset with Sort/Filter

249097 HOWTO: Re-Shape a Hierarchical Recordset

196968 PRB: SHAPE Provider Pulls Down All Records in Child Table

189657 HOWTO: Use the ADO SHAPE Command

213856 INFO: Using Disconnected Hierarchical Recordsets

191744 HOWTO: Extract Child Recordsets from Shaped Recordset

Keywords: kbwindowsforms kbdatabinding kbfilter kbvbx kbdatabase kbprb KB331917

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.