Setting Report RecordSource from form code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Luke Bailey - 03 May 2007 05:08 GMT

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?

Reply to this Message

fredg - 03 May 2007 05:52 GMT

> 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?

DoCmd.OpenReport "ReportName", acViewDesign
Reports!ReportName.Recordsource = "Select blah blah"
DoCmd.Close acReport, "ReportName", acSaveYes
DoCmd.OpenReport "ReportName", acViewPreview

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Allen Browne - 03 May 2007 12:53 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>> I have a form called "frm_report_select" where users make some
>> selections,
[quoted text clipped - 9 lines]
> DoCmd.Close acReport, "ReportName", acSaveYes
> DoCmd.OpenReport "ReportName", acViewPreview

Reply to this Message

fredg - 03 May 2007 17:40 GMT

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

Allen,
It does work. Notice that I'm opening it in design view first.

> DoCmd.OpenReport "ReportName", acViewDesign
> Reports!ReportName.Recordsource = "Select blah blah"
> DoCmd.Close acReport, "ReportName", acSaveYes
> DoCmd.OpenReport "ReportName", acViewPreview

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Allen Browne - 03 May 2007 18:21 GMT

Yes of course: I missed the design view.

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>> Fred, while setting the RecordSource works for forms, I don't think you
>> will
[quoted text clipped - 8 lines]
>> DoCmd.Close acReport, "ReportName", acSaveYes
>> DoCmd.OpenReport "ReportName", acViewPreview

Reply to this Message

Allen Browne - 03 May 2007 05:58 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 3 lines]
> there
> any way to change the recordset from code on the form VBA?

Reply to this Message