[RESOLVED] need help with Data report groups

i need help badly with this one

i need to show this data report using shape sql

here's some code

VB Code:

Option Explicit
Dim cnRep As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim com As New ADODB.Command
 
Private Sub DataReport_Initialize()
    cnRep.CursorLocation = adUseClient
    cnRep.Provider = "MSDataShape"
    cnRep.Open "Data Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & ConnectStringVal & ";Jet OLEDB"
    com.CommandText = "SHAPE {SELECT ReceivingDetails.RefNo As RefNo, Products.PartNo As PartNo, Products.Description As Description, ReceivingDetails.Qty As Qty, Receiving.CurrencyUsed & Format(ReceivingDetails.UnitPrice,'###,###,###,##0.00') As UnitPrice, Receiving.CurrencyUsed & Format(ReceivingDetails.SubTotal,'###,###,###,##0.00') as SubTotal, ReceivingDetails.ReceivingNo FROM ReceivingDetails, Products, Receiving WHERE ReceivingDetails.ProdCode = Products.ProductCode AND ReceivingDetails.ReceivingNo = Receiving.ReceivingNo AND ReceivingDetails.ReceivingNo = 'RR-060000001'} As rsReceiving APPEND ({SELECT SerialNo, ReceivingNo FROM ReceivingSerials} AS rsSerials RELATE ReceivingNo to ReceivingNo)"
    com.ActiveConnection = cnRep
    com.CommandType = adCmdText
    com.Execute
    
    rs.ActiveConnection = cnRep
    rs.CursorLocation = adUseClient
    rs.Open com
    Set rptRec.DataSource = rs
    
    With rptRec
        With .Sections("rsReceiving_Header")
            .Controls("txtRefNo").DataMember = "rsReceiving"
            .Controls("txtRefNo").DataField = rs.Fields(0).Name
            .Controls("txtPartNo").DataMember = "rsReceiving"
            .Controls("txtPartNo").DataField = rs.Fields(1).Name
            .Controls("txtDesc").DataMember = "rsReceiving"
            .Controls("txtDesc").DataField = rs.Fields(2).Name
            .Controls("txtQty").DataMember = "rsReceiving"
            .Controls("txtQty").DataField = rs.Fields(3).Name
            .Controls("txtUnitP").DataMember = "rsReceiving"
            .Controls("txtUnitP").DataField = rs.Fields(4).Name
            .Controls("txtSTtl").DataMember = "rsReceiving"
            .Controls("txtSTtl").DataField = rs.Fields(5).Name
        End With
        .Sections("rsSerials_Detail").Controls("txtSerial").DataMember = "rsSerials"
        .Sections("rsSerials_Detail").Controls("txtSerial").DataField = rs.Fields(0).Name
    End With
End Sub

and heres the design of my data report

You did not specify the problem but I noticed these two

Do not set the DataMember property for controls whose DataField property references a field in the Parent recordset.

To Reference Field names in the Child recordset use

rs.Fields(2).Value.Fields(1).Name

thnx bruce, actually the problem i got with it is that when i run that code, it give out an error showing DataField rsReceiving.Qty not found.

and last night, due to my despair, i commented out those lines setting the datamember property of the rptTextboxes and furtunately, it worked.

anyway, thanks for the reply, thats additional idea and explanation y i encounter such problem