How do you display a record set inside a report in Microsoft access

Question

I am trying to display the results of a recordset inside of a report that I made, my process is that I select a year on a form then click a button, once that button is clicked it sends that year to the report and runs the following Report_Open code:

Private Sub Report_Open(Cancel As Integer)
    Dim rst As Recordset
    Dim qdf As QueryDef
    Dim subReport As Report
    If Not IsNull(OpenArgs) Then
        
        Set qdf = CurrentDb.QueryDefs("qry")
        qdf.Parameters("[Year]").Value = OpenArgs
        Set rst = qdf.OpenRecordset()
        qdf.Close

        'Code to fill the report'
        
        '''''''''''''''''''''''''          

    Else
        DoCmd.Close
    End If
End Sub

I am trying to get the results of that recordset to be displayed on my Report. due to the nature of the query, the results can vary drastically so I can't just set the results in a set amount of textboxes.

I was trying to set the Report's RecordSource to this recordset but I learned you can't do that with recordsets. I'm very new to how to make reports so I've been stuck on this for a few days and feel a bit defeated. Any help is really appreciated.

Answer 1

This is not how you do it in Access. Directly assign your query name to the record source of your report. Access will then create the Recordset automatically.

You can then open the report by specifying a where-clause:

DoCmd.OpenReport "rptReport", acViewPreview, WhereCondition:="[YearColumn] = " & theYear

If you have to compare the year against a Date/Time column, the condition will look like this: "Year(myDateColumn) = " & theYear

Answer 2

While a form allows a created recordset to be assigned, reports do not.

There are two common solutions:

While a report does not allow a recordset assignment, you CAN assign the SQL, and that SQL thus can be dynamic.

Hence, you can do this:

  dim strSQL         as string
  strSQL = "SELECT * FROM tblHotels WHERE City = 'Edmonton' ORDER BY HotelName"
  ' above sql thus can be anything you want
  docmd.Openreport "rptMyreport",acViewPreview,,,,strSQL

And in the reports open event, you can have this code:

Private Sub Report_Open(Cancel As Integer)

    Dim strSQL          As String
    
    strSQL = Me.OpenArgs
    
    Me.RecordSource = strSQL
    

End Sub

So, you simple skip the creating of the recordset, and skip trying to assign the reocrdset to the reports data source (it is not allowed).

However, as above shows, you can most certainly assign dynamic SQL to the report.

The second approach, and one I found over the years to cover 99% of such cases?

Do as suggested by Oliver above.

Simply pass the conditions (where clause) to the report. This where clause can even include sub queries!!!

So, now the open form command becomes this:

docmd.OpenReport "rptHotels", acViewPreview,,"City = 'Edmonton'"

I have rare required to set the SQL for a report when the simple "where" clause can be used to supply criteria. With above passed "where" clause, then remove the above sample code in the reports on-open event.

However, you can build custom SQL and assign that to the reports RecordSource.