sort forces recordset clone?

[JString]

Hey there.

I've been experimenting with using a shared recordset object so that the
various subforms in my app will be linked to some degree. It works fine
until a sort button is pressed. When this happens, one subform will be
sorted but the others will not, and this comparison statement returns false:

forms("form1").recordset is forms("form2").recordset

So it looks like the sort button forces each form's recordset property to
change to a recordsetclone. Does anyone know of a better way?

 

[Allen Browne]

Correct. Even if Form2 is assigned the Recordset of Form1, Form2 becomes
stand-alone (with its own Recordset) if you close Form1 or apply a Filter or
OrderBy to it.

Your line of code correctly indicates that it is no longer the same
recordset. The following code reports that there is one recordset open
before you re-order Form1, and afterwards there are two. The code goes into
the Click event procedure of a button on Form2:

Private Sub cmdTest_Click()
Debug.Print DBEngine(0)(1).Recordsets.Count

Forms(0).OrderBy = "CITY"
Forms(0).OrderByOn = True

Debug.Print Forms(1).Recordset Is Forms(0).Recordset

DBEngine(0)(0).Recordsets.Refresh
Debug.Print DBEngine(0)(1).Recordsets.Count
End Sub