Data Report w/Grouping Refresh

[troyertm]

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

[divined]

Are you using the RDC for your reports or are you importing an external rpt file? In the first case, the only way around this problem I`ve found is to completely unload the form housing the report. If anyone knows how to get around this problem in a more elegant fashion I`d be glad to listen.

[goateeeth]

any other way? still not working on my case

[dee-u]

Try this....

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

[divined]

Yes, this is much or less what I`ve settled for. But, I had many problems when I didn`t open the Crystal report viewer in a non modal form with a changing recordset. For example consider the following case :

a) A user opens a form with the Crystal Reports viewer control which opens a connection to the database, creates an ADODB.REcordset ,sets the DataSource property and finally opens the viewer.
b) Then the user switches form (e.t.c. minimizes the Viewer form and makes another form active).
c) In the other form he/she edits the records already being displayed in the Viewer form.

In this case you cannot simply refresh the data. What I`ve tried doing is :

a) Refresh the ADODB.Recordset object.
b) Refresh the Viewer control.

Nonetheless, for some reason the Viewer control would display a report using the old Recordset. I never found a way around this!

[dee-u]

I guess you should just show them modally.