asked on

Access report based on calculated recordset

Hello:

I need to generate a report that is based on a calculated recordset. The calculated recordset is not based on a table or query. Thus when I set the recordsource of the report to the recordset source, the report opens empty eventhough the recordset contains data. I am using the following code.

Report.Recordsource = Recordset.Source.

This seems to work with recordsets that are based on a table or query. How am I able to pass the data in my recordset to the report for display. Please help.

Thank you.

Data-Man

I have tried to do this in the open event of the report, but I don't think it works even though the report recordset object supports it.

Set Me.Recordset = rstTemp 'Where rstTemp is a recordset object.

I think I ended up putting the recordset into a temp table and then basing the report on the temp table.

Mike

Markus Fischer

The property .Recordset of a report is the recordset used as source for the report. .RecordSource is the name of the query or table, or the SQL used to create the report's recordset. Thus:

Report.RecordSource = Recordset.Source

basically assigns as RecordSource the string used to create the report's recordset (it's source), which happens to be the report's RecordSource.

This is nonsensical.

What gets me wondering is the following:
* "The calculated recordset is not based on a table or query" and...
* "the recordset contains data"

How to you manage that. Where does the data come from, then?

Cheers!

FJCHAVEZ

ASKER

Hi Data-Man and Harfang:

Thanks for the response. My application is a .adp front end with a sql back end. Because I will have many users accessing the aplication simultaneously, I trying to stay away from storing the data into a temp tables. Basically what I am doing is declaring two recordsets. One connects to a table and queries the values in a table. I then perform calculations in VB and store those values in a second recordset. The following is an example of basically how I am doing this using a simpler calculation.

Dim rst1 as ADODB.recordset
Dim rst2 as ADODB.recordset

set rst1 = new ADODB.recordset
set rst2 = new ADODB.recordset
...
...

rst1.open query

rst2.fields.append value1, adVariant

rst1.movefirst
for i = 1 to rst1.recordcount
rst2.addnew
rst2.fields(0) = rst1.fields(0) + sqr(rst1.fields(1))
rst1.movenext
next i

DoCmd.OpenReport report1, acViewDesign
Reports("Report1").Recordsource = rst2.source
DoCmd.OpenReport report1, acViewPreview,,,acWindowNormal

This works and opens the report but with no data. I am also able to iterate through rst2 and check the recordset contains the right data. My objective is to send the data to the report.

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Markus Fischer

Addendum: the above is for an unbound report, containing an unbound control txtValue...