Making the RecordsetType Property of a Report Snapshot (1 Viewer)

[sneuberg]

I would like to change the RecordsetType of a report to snapshot.
http://www.access-programmers.co.uk/forums/newthread.php?do=newthread&f=10 says in part "Changing the RecordsetType property of an open form or report causes an automatic recreation of the recordset." yet I can't find anyway to change the RecordsetType of a report. Anyone know how to do this or make the record source query of the report a snapshot query.

 

[Uncle Gizmo]

What problem are you trying to solve?

 

[sneuberg]

The report is base on a table that gets created when the the report is run. If the user runs the report when the report is already open the code can't recreate the table. So I thought a snapshot might solve that. For now I'm just closing the report if it's open before creating the table and running the report. So that's not a big problem right now. But I don't know how to open a report with a snapshot recordset. That's the problem I'm trying to solve.

 

[Uncle Gizmo]

How do you call the report?

 

[sneuberg]

How do you call the report?

DoCmd.OpenReport "Output Report", acViewReport, "", "", acNormal

 

[Uncle Gizmo]

The report is base on a table that gets created when the the report is run

What event calls the code that creates the table?

 

[sneuberg]

A command button event. The subroutine CreateReport in the code below creates the table

Private Sub OpenReport_Click()
On Error GoTo OpenReport_Click_Err


If CurrentProject().AllReports("Output Report").IsLoaded Then
    DoCmd.Close acReport, "Output Report"
End If

'CreateReport creates the table on which the Output Report is based'
CreateReport
DoCmd.OpenReport "Output Report", acViewReport, "", "", acNormal


OpenReport_Click_Exit:
    Exit Sub

OpenReport_Click_Err:
    MsgBox Error$
    Resume OpenReport_Click_Exit

End Sub

 

[jdraw]

sneuberg,

Can you tell us a little more about the table that is created and the report?

Usually you have data in tables, then maybe select some options from a form, and open a report with some parameters.

Not clear on why the table is recreated, then report opened. You could change the report's recordsource???

 

[sneuberg]

A forum member had an unusual requirement for grouping data by years. See http://www.access-programmers.co.uk/forums/showthread.php?p=1460971#post1460971

I couldn't figure out how to do it with a query so I did it brute force with vba and created the report data in a table. The prototype database has been uploaded in the last post of the above thread. If you have the time I'd like for someone to test this and see if they can break it.

 

[Uncle Gizmo]

Well I've had a very interesting evening chasing my tail!

I am aware that you can create persistent recordsets, so I thought it would be possible to create and save a persistent recordset, and then assign it to the report. However it appears this functionality is not available to a report.

According to the error message, it says it's only available in ADP. However I think that's a red herring.

Anyway if anyone's interested I have attached my scratchpad database where I have been faffing around with this for the whole evening!

The only lead I haven't followed up is that if the recordset can be generated with DAO, then I understand you can asign the DAO recordset by its name. This isn't available in ADO recordsets.

The other thing I thought I could try was to save the record set in XML format.