[RESOLVED] need help with Data report groups

[d3gerald]

i need help badly with this one

i need to show this data report using shape sql

here's some 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

[brucevde]

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

[d3gerald]

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