Setting Report RecordSource from form code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form called "frm_report_select" where users make some selections,
then a generic report opens. I would like to change the recordset of the
report based on users selections. Is there any way to do this from code of
the form? I know how to change on the OnOpen from the report, but is there
any way to change the recordset from code on the form VBA?
 
I have a form called "frm_report_select" where users make some selections,
then a generic report opens. I would like to change the recordset of the
report based on users selections. Is there any way to do this from code of
the form? I know how to change on the OnOpen from the report, but is there
any way to change the recordset from code on the form VBA?
Click to expand...

DoCmd.OpenReport "ReportName", acViewDesign
Reports!ReportName.Recordsource = "Select blah blah"
DoCmd.Close acReport, "ReportName", acSaveYes
DoCmd.OpenReport "ReportName", acViewPreview
 
The best option would be to leave the report's RecordSouce alone. Instead
use the WhereCondition of OpenReport when you open it from your form. The
WhereCondition can be quite involved, with lots of fields, perhaps generated
like this filter string:
http://allenbrowne.com/ser-62.html

If that is not practical, the next best idea is to set the RecordSource of
the report in its Open event, which you already know about.

In some cases (e.g. subreports), you might use a query as the source of the
report, and write to the SQL property of the QueryDef before you OpenReport,
e.g.:
CurrentDb.QueryDefs("Query1").SQL = "SELECT ...

The least desirable solution would be to place code in your form that opens
the report in design view in order to set is RecordSource. This approach
means you can never create an MDE.
 
Fred, while setting the RecordSource works for forms, I don't think you will
be able to do that once the report has opened (i.e. any later than
Report_Open.) Likely to get error 2191.
 
Fred, while setting the RecordSource works for forms, I don't think you will
be able to do that once the report has opened (i.e. any later than
Report_Open.) Likely to get error 2191.
Click to expand...

Allen,
It does work. Notice that I'm opening it in design view first.
 
Yes of course: I missed the design view.

(I don't do that since it means the app can't be an MDE.)
 
Back
Top