Access report based on calculated recordset

Posted on 2004-10-31
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
    LVL 18

    Expert Comment

    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

    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

    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.

    LVL 58

    Accepted Solution

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    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 Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    911 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now