Microsoft KB Archive/331917

= 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(&quot;Orders&quot;).Value.Filter = &quot;OrderId = 3&quot; the following sequence of events occurs:
 * rs.Fields(&quot;Orders&quot;).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 = &quot;OrderId = 3&quot; sets the filter on the newly opened recordset to &quot;OrderId = 3&quot;.
 * 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.



Steps to reproduce the behavior
 Create a new Visual Basic project.  Add a command button to the form, and then set the following properties. Name = cmdFilterChild Caption = Filter Child  On the Project menu, click References. Add a reference to Microsoft ActiveX Data Objects 2.0 Library.  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 &quot;Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB&quot; ' Open the hierarchical recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open &quot;SHAPE {SELECT * FROM Employees} AS Employees APPEND ({SELECT * FROM Orders} &quot; & _     &quot;AS Orders RELATE EmployeeID TO EmployeeID)&quot;, cn, adOpenStatic, adLockBatchOptimistic, adCmdText ' Set a filter on the child records. MsgBox &quot;Child filter before setting the filter property : &quot; & _ rs.Fields(&quot;Orders&quot;).Value.Filter rs.Fields(&quot;Orders&quot;).Value.Filter = &quot;OrderId = 3&quot; ' Display the OrderId set in previous step MsgBox &quot;You expect the Child filter to be 'OrderId = 3'. However, you receive: OrderId = &quot; & _ rs.Fields(&quot;Orders&quot;).Value.Filter & _ &quot;. This behavior occurs because ADO closes the Recordset because of No Reference.&quot; ' This code references this recordset. ' The recordset stays open because the user is holding a reference to it. Set rs2 = rs.Fields(&quot;Orders&quot;).Value rs2.Filter = &quot;OrderId = 3&quot; MsgBox &quot;Child filter is now 'OrderId = 3' and it persists: &quot; & rs2.Filter & _ &quot;. This behavior occurs because ADO has Reference.&quot; End Sub  Modify the Data Source connection string to correctly reflect the path of your NWIND.mdb.</li> Save, and then run the sample code. Notice that a message box appears that indicates the progress.</li></ol>

<div class="references_section">