Access report based on calculated recordset


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.
Who is Participating?
harfangConnect With a Mentor Commented:
I made a fool of myself :)
Me.Recordset does not exist, because Access builds one or several queries based on .RecordSource to provide for:
* Sorting and Grouping
* Data for group headers and footers
* Including all the domain functions in those (e.g. Sum())

So you can't use the report's Recordset, because there is none (or rather there are several, not available)


You can program your own... Provided the "rst2" above is available globally, I tried:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    txtValue = rst2!value1
    Me.NextRecord = rst2.EOF

End Sub

Private Sub Report_Open(Cancel As Integer)

    Cancel = rst2.EOF

End Sub

It worked fine, of course without sorting or grouping :)

To correct my previous comment, your "Recordset.Source" meant "rst2.Source" from later. Then try to inspect rst2.Source, it's an empty string, so that nothing can happen.

If you do need sorting and grouping, you will need to create a temp table, I guess.

Good Luck
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.

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?

FJCHAVEZAuthor Commented:
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
... query

rst2.fields.append  value1, adVariant

for i = 1 to rst1.recordcount
  rst2.fields(0) = rst1.fields(0) + sqr(rst1.fields(1))
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.

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

From novice to tech pro — start learning today.