[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Access report based on calculated recordset

Posted on 2004-10-31
Medium Priority
Last Modified: 2008-03-17

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.
Question by:FJCHAVEZ
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 18

Expert Comment

ID: 12460218
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.

LVL 58

Expert Comment

ID: 12460357
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?


Author Comment

ID: 12460538
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

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.

LVL 58

Accepted Solution

harfang earned 1000 total points
ID: 12460685
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
LVL 58

Expert Comment

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question