Can I use a hierarchical recordset chapter as a subform recordset?

[JR]

I can't seem to find much info at all on this one.. I'd like to to
bind to a disconnected recordset on my parent form and use the chapter
to bind to in the subform. I thought this would acheive automatic
synchronization since navigating through the parent set filters the
chapters accordingly, but the code below simply displays all child
records in the sub form at all times. why doesn't this work? what is
the right way to do this? is the only way to set filters on a chapter
recordset clone for each parent record on the current event?


Private Sub Form_Load()
Dim workRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection

cnn.Provider = "MSDataShape"
cnn.Properties("Data Provider") = "SQLOLEDB"
'...connection info...

cnn.Open
If Not Me.RecordSource = " " Then

workRS.CursorLocation = adUseClient

workRS.Open "SHAPE {select * from tblsite} " _
& "APPEND " _
& "( {SELECT * From tblsitecomponent} RELATE siteid to
siteid ) as components" _
, cnn, adOpenKeyset, adLockBatchOptimistic
workRS.ActiveConnection = Nothing



Set Me.Recordset = workRS

'set subform recordsource
Set Me.Controls("sensors").Form.Recordset = workRS("components").Value

workRS.Close
Set workRS = Nothing
End If
End Sub

 

[JR]

I'm using an access 2003 project connecting to sql server 2005

 

[Paul Shapiro]

It's been a long time since I was working with shaped recordsets, but my
memory is that the child recordset contains all the child records, not just
the related child records, even if the parent recordset is filtered to
include a single record. If you only wanted a single parent row, then
filtering the child recordset to return just that one parent's child should
work. If you want multiple parent rows, then I would think that setting the
subform control's Link Master Fields and Link Child Fields property should
provide the correct synchronization. This would be the same settings you'd
use for the subform in a "normal" form-subform situation.

 

[JR]

It's been a long time since I was working with shaped recordsets, but my
memory is that the child recordset contains all the child records, not just
the related child records, even if the parent recordset is filtered to
include a single record. If you only wanted a single parent row, then
filtering the child recordset to return just that one parent's child should
work. If you want multiple parent rows, then I would think that setting the
subform control's Link Master Fields and Link Child Fields property should
provide the correct synchronization. This would be the same settings you'd
use for the subform in a "normal" form-subform situation.














- Show quoted text -

Thanks for your response, Paul. but with a bit more research I've
realized the difficulty of working in adp with unbound forms/
disconnected recordsets. Microsoft even explicitly states in a white
paper that it has no elegant solution for that situation (binding
disconnected recordsets to forms). So the only option is complete
coding by hand of unbound forms (or VB or .net I suppose) so, not
surprisingly I took the hint and decided to put the time into
optimizing performance with an immediate connection. not such a bad
thing i suppose, but it would have been great to grab needed records
at the beginning, unplug, edit, plug in and batch update.