[RESOLVED] Report sections do not match

[phpman]

When I add the Group header, i get the following error "Report sections do not match data source" when running. How do i avoid this error. Section 1 is my group header, SQL_Detail is my section for listing the results.

And I do not use a data environment.

Dim rpt As New RptBydept
            
            rs.Open "SELECT Machine, UserID, [Software Title], Version, Department, [Assigned Date], Server FROM [MS Office Licenses] WHERE " & s & "  and UserID NOT LIKE '--%' GROUP BY Department,Machine, UserID, [Software Title], Version,[Assigned Date], Server ORDER BY [" & CmbOrderby1.Text & "], [" & CmbOrderby2.Text & "]", Cn, adOpenStatic, adLockOptimistic
            
            'Update fields and datareport control names to those you use
            'Set datasource and bind recordset fields.
            Set rpt.DataSource = rs
           
            
            With rpt.Sections("SQL_Detail")
               
                .Controls("Text1").DataField = "UserID"    'where quantity is a field in the recordset
                .Controls("Text2").DataField = "Software Title"
                .Controls("Text3").DataField = "Version"
                .Controls("Text4").DataField = "Department"
                .Controls("Text5").DataField = "Server"
                .Controls("Text6").DataField = "Assigned Date"
                .Controls("Text7").DataField = "Machine"                
            
            End With
            rpt.Sections("Section2").Controls("Label15").Caption = rs.RecordCount
            rpt.Sections("Section1").Controls("Label17").Caption = area
            rpt.Show vbModal
    
            Unload rpt
            Call DBDisconnect
            Set rpt = Nothing
            'Unload FrmReports
            Exit Sub

[brucevde]

Grouping on the DataReport requires a "Hierarchical Recordset" (kind of like a recordset within a recordset). You need to use the MSDataShape provider and Shape Commands to create the recordset.

Here is a good place to start

http://support.microsoft.com/kb/194516

[phpman]

yeah, I thought I had to use SHAPE but every time I do I get this error

"Invalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure', 'Select' or 'Update'

rs.Open "SHAPE{SELECT Machine, UserID, [Software Title], Version, Department," & _
            "[Assigned Date], Server FROM [MS Office Licenses] WHERE " & s & "  and " & _
            "UserID NOT LIKE '--%' ORDER BY [" & CmbOrderby1.Text & "], [" & CmbOrderby2.Text & "]}" & _
            "AS SQL1 COMPUTE SQL1 BY Department", Cn, adOpenStatic, adLockOptimistic

[brucevde]

The SQL statement is fine. The problem is with the ADO Connection.

You must use the MSDataShape provider.

cn.Open "provider=msdatashape;data provider=microsoft.jet.oledb.4.0;data source=m:\testing\nwind2.mdb"

[phpman]

ok, tha tseems to have gotten rid of that error. so now the question is, does the datafields go into the group header or keep them in the details section. As of now I have them in the SQL details section. I get an error that states

DataField "Software Title" not found.

[brucevde]

Your SQL statement will create a recordset with 2 fields, called Department and SQL1. The SQL1 field has a datatype of adChapter which is basically another Recordset. That second recordset is the one that contains the fields/data from the Select statement.

So for report controls bound to a field in this adChapter recordset you must set the DataField and the DataMember properties. In this case the DataMember would be set to SQL1.

[phpman]

ok, so for that I have this

Set rpt.DataSource = rs

How do I add the SQL1 to that if it is bound to rs?

[brucevde]

The Set rpt.DataSource = rs statement is correct.

You need to set the DataMember property for each control

With rpt.Sections("SQL_Detail")
   .Controls("Text1").DataField = "UserID"
   .Controls("Text1").DataMember = "SQL1"
   .Controls("Text2").DataField = "Software Title"
   .Controls("Text2").DataMember = "SQL1"
  etc...

[phpman]

Oh ok, I will do that and see how it goes.

[phpman]

Thank you, that worked a charm. No to fix my headers and be done with this

Once again, thanks.