Grouping on Data Report

Posted on 2006-05-30
Last Modified: 2010-04-30
I am using the VB6.0 Data Report Writer to create a report with 1 group level.  This is the classic Order Heading and detail report.  I have been successful creating the report based on an entry in the data environment.  I created a command called 'qryOrderRpt' and then specified that it should be grouped on field 'OrderHdrID'.  The sql specified in the properties is:

SELECT tblOrderHdr.OrderHdrID, tblOrderHdr.ClientID, tblOrderHdr.PersonnelID, tblOrderHdr.OrderDate, tblOrderHdr.PurchaseOrder, tblOrderHdr.Bin, tblOrderHdr.SalesTax, tblOrderHdr.Shipping, tblOrderHdr.PrePaid, tblOrderDtl.idItem, tblOrderDtl.Sequence, tblOrderDtl.Name, tblOrderDtl.Qty,tblOrderDtl.Description, tblOrderDtl.Color, tblOrderDtl.[Size], tblOrderDtl.UnitPrice, tblOrderDtl.Status
FROM tblOrderHdr INNER JOIN tblOrderDtl ON tblOrderHdr.OrderHdrID = tblOrderDtl.OrderHdrID;

On the data report I dragged OrderHdrID into the group level and the fields 'Qty, Description and Color' into the detail level.  It works as designed except now I want to create the datamember behind the report as a recordset,  using code.  I have done this many times before by creating the recordset (rs) and then specifiying

    Set .DataSource = rs
    .DataMember = rs.DataMember

However, I am runnign into a problem on the grouping, I'm guessing I just don't have the sql specified to group the data but I'm not sure how to do that.  This is what I have:

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
sqlString = "SELECT tblOrderHdr.OrderHdrID,  " & _
            "tblOrderHdr.ClientID,  " & _
            "tblOrderHdr.PersonnelID,  " & _
            "tblOrderHdr.OrderDate, " & _
            "tblOrderHdr.PurchaseOrder, " & _
            "tblOrderHdr.Bin,  " & _
            "tblOrderHdr.SalesTax, " & _
            "tblOrderHdr.Shipping,  " & _
            "tblOrderHdr.PrePaid,  " & _
            "tblOrderDtl.idItem,  " & _
            "tblOrderDtl.Sequence,  " & _
            "tblOrderDtl.Name,  " & _
            "tblOrderDtl.Qty, " & _
            "tblOrderDtl.Description,  " & _
            "tblOrderDtl.Color,  " & _
            "tblOrderDtl.[Size],  " & _
            "tblOrderDtl.UnitPrice,  " & _
            "tblOrderDtl.Status   " & _
            "FROM tblOrderHdr   " & _
                "INNER JOIN tblOrderDtl ON tblOrderHdr.OrderHdrID = tblOrderDtl.OrderHdrID"

sqlString = sqlString & whereCond
rs.Open sqlString, cn

If rs.RecordCount = 0 Then
    MsgBox "There are no records to report which meet the criteria you entered.", vbOKOnly, CompanyName
    Exit Sub
End If

With tstOrderRpt
    Set .DataSource = rs
    .DataMember = rs.DataMember
    .LeftMargin = 700
    .RightMargin = 700
    .TopMargin = 750
    .BottomMargin = 750
End With

But when I run the report I get 'Report Sections do not match data source'.  What do I need to do to resolve this issue?


Question by:mlcktmguy
    LVL 18

    Accepted Solution

    To get grouping in a report you need to use a "MSDataShape" recordset.

    The syntax for creating these is not very easy to learn but you can create them in the data environment and then get the shaped SQL query in the data environment and copy it into code.;EN-US;q289793

    LVL 15

    Expert Comment

    try downloading the service pack 6
    LVL 1

    Author Comment

    I have service pack 6 installed.  What would you expect to be different.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now