Disconnected hierarchical rs and DataReport

[Jaroslaw]

Hi all,

In my project I have to use a disconnected ADO recordset to fill a DataReport. The amount of data processing required for the report prevents me from using any SQL. I have to create and populate the recordset in memory and then use it as DataSource for the report. Everything works fine until I try to use group headers. I have read that one has to create a hierarchical recordset to accomplish this.
So I have done so like this:

Private Function BuildRecordset() As ADODB.Recordset 
    Dim rsParent As ADODB.Recordset 
    Dim rsChild As ADODB.Recordset 
    Dim i, j As Integer 
     
    Set rsParent = New ADODB.Recordset 
    rsParent.Fields.Append "ParentField1", adVarChar, 20 
    rsParent.Fields.Append "ChildRs", adVariant 
     
    Set rsChild = New ADODB.Recordset 
    rsChild.Fields.Append "ChildField1", adVarChar, 20 
     
    rsParent.Open 
    rsChild.Open 
     
    For i = 1 To 5 
        rsParent.AddNew 
        rsParent.Fields("ParentField1").Value = "Parent" & i 
        For j = 1 To 5 
            rsChild.AddNew 
            rsChild.Fields("ChildField1") = "Parent" & i & ", Child" & i 
            rsChild.Update 
        Next 
        rsParent.Fields("ChildRs").Value = rsChild 
        rsParent.Update 
    Next 
     
    Set BuildRecordset = rsParent 
    Set rsChild = Nothing 
    Set rsParent = Nothing 
End Function 
[\Code]

Then I bind the report fields in code like this: 

[Code]
    'group header 
    DataReport1.Sections("Section6").Controls.Item("txtGroupField1").DataMember = "" 
    DataReport1.Sections("Section6").Controls.Item("txtGroupField1").DataField = "ParentField1" 
     
    'detail 
    DataReport1.Sections("Section1").Controls.Item("txtDetailField1").DataMember = "ChildRs" 
    DataReport1.Sections("Section1").Controls.Item("txtDetailField1").DataField = "ChildField1" 
[\Code]

When I try to print I get the following error: 
"DataField 'ChildRs.ChildField1' not found" 

How do I solve this? Or am I going about it the wrong way? I would like to avoid using the Data Environment if possible.

 

[Jaroslaw]

Anybody?

Although, I would like to avoid using DataEnvironment, is it possible to create a "disconnected" DataEnvironment (without SQL) and populate it manually like you can do with an ADO recordset?

Thanks for any suggestions,
Jaroslaw

 

[jopaumier]

Jaroslaw,

I am very new to hierarchical recordsets, but I did need to do something similar (I had grandchildren), and tended to go overboard on notation to keep it all straight, so I think the suggestion below has some redundancy in it. If it is wrong, hopefully someone more knowledgeable can correct me and get you on the right track.

DataReport1.Sections("Section1&quot.Controls.Item("txtDetailField1&quot.DataField = rsChild.Fields("ChildField1&quot.Name

Good luck,
Jim

 

[Jaroslaw]

Thanks for your reply Jim.
As it turns out this part was fine (binding the report fields) but I was going completely wrong about creating the hierarchical recordset.
Here is the proper code if anyone is interested:

Private Function BuildShapedRecordset() As ADODB.Recordset
    Dim oConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=MSDataShape;Data Provider=NONE;"
     
    strSQL = "SHAPE APPEND NEW adChar(20) AS ParentField1," & _
                " NEW adChar(20) AS Ref1," & _
                " ((SHAPE APPEND NEW adChar(20) AS ChildField1," & _
                " NEW adChar(20) AS Ref1)" & _
                " AS ChildRs RELATE Ref1 TO Ref1)"
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, oConn, adOpenStatic, adLockOptimistic, -1

    Set BuildShapedRecordset = rs
    Set rs = Nothing
    Set oConn = Nothing
End Function
[\code]

Jaroslaw