Recordset Grouping for Data Reports

[troyertm]

I already posted this in the reporting forum, but I think my question is more specific to the actual command I want to use with my recordset, so I'm posting again here. Can anybody help me with the following issue?

I have a database with a table called "Results" that I often need to print data from. When the data is first inserted, a report in generated, but I need to be able to regenerate old reports based on the date they were originally printed (which is stored in a field in my Results table). For ease, or so I thought, I made a temporary table to store the data that needs printed, since not all of the data in the Results table will need to be inserted into my report. I connected that table, called TempStore, to a data access object in Visual Basic, and then setup grouping on multiple fields, which reflects in my data report.

The report works exactly as I want, grouping by about 5 fields, and then showing details line for each grouping; however, if I try to run the report during the same run of the program, but with a new set of data, it still shows the old data. Regardless of the fact the report has been closed, it seems to store all the data from the original running of the report. It's my understanding that setting the datasource of the data report to an ADO RecordSet object would solve the problem. I've been able to get this to work with simple queries, but not with grouping. Below is a sample I found for refreshing a simple data report. Does anybody know how to modify the SQL query to work with groupings?

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\books.mdb;" & _
"Persist Security Info=False"
Set rs = conn.Execute("SELECT Title, Year, URL, Pages, " & _
"ISBN FROM BookInfo ORDER BY Title")
Set rptTitles.DataSource = rs
rptTitles.Show vbModal

[brucevde]

Please explain what you mean by Grouping?

Something tells me this is not the answer you are looking for

SELECT Title, Year, URL, Pages, ISBN
FROM BookInfo
Group By Title, Year
ORDER BY Title

[troyertm]

Going by the example using Title, Year, URL, Pages, and ISBN fields, let's say I wanted to make a data report that grouped by Year, so that there would be a Year heading, with a listing of all titles under that year. Since the Year value for all these books is the same, it would not be shon for each record. The Visual Basic Data Report requires you to add a group section in order to accomplish this, but using the following statement does not work:

SELECT Year, Title FROM BookInfo GROUP BY Year SORT BY Year

This tells me, "You tried to execute a query that does not include the specified expression 'Title' as part of an aggregate function."

I tried doing the following as well:

SELECT Year, Title FROM BookInfo SORT BY Year

Then it tells me that, "The report sections do not match the datasource."

This seems to be because I have the Year in the Group Section and the Title in the details section, but I have nothing in the statement to tell the report that the Year should be a grouping.

Does this make sense? Am I going about this wrong?

[brucevde]

Are you familiar with Hierarchical recordsets and Data Shaping? If you want to use "grouping" in your report, you must use a Hierarchical recordset.

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set db = New ADODB.Connection
db.Open "provider=msdatashape;data provider=sqloledb;data source=handel;initial catalog=northwind;integrated security=sspi"
 
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Shape {SELECT CustomerId From Orders} Append ({Select OrderDate, CustomerId From Orders} Relate CustomerId To CustomerId) As ChildRecord", db, adOpenStatic, adLockReadOnly, adCmdText
Set rs.ActiveConnection = Nothing
 
db.Close
Set db = Nothing
 
Dim rep As New DataReport1
 
Set DataReport1.DataSource = rs
DataReport1.Show

In the Data Report
Add a TextBox to the Group section and set the DataField property to CustomerId

Add a TextBox to the Detail section.
Set the DataField property to OrderDate
Set the DataMember property to ChildRecord

[sarastanley]

I am having the same problem did you solve the problem. I used the same code but I keep getting errors