Setting Report Recordsource

[Simon Harris]

Hi All,

I'm trying to set the recordsource of a report.

Heres what I have:

DoCmd.OpenReport "RPT_currently_viewed_customers", acViewPreview
Set rpt = Reports("RPT_currently_viewed_customers")
rpt.RecordSource = Forms!frm_switchboard!SUBFRM_view_customers.RowSource

This runs, but I get the following error:

Run-time error '2191':
You can't set the record source property in print preview of after
printing has started.

So, I thought Ok, fair enough - I'll set the recordsource property before I
open the report, and changed the code to:

Set rpt = Reports("RPT_currently_viewed_customers")
rpt.RecordSource = Forms!frm_switchboard!SUBFRM_view_customers.RowSource
DoCmd.OpenReport "RPT_currently_viewed_customers", acViewPreview

Which gives me this error:

run-time error '2451':
The report name 'RPT_currently_viewed_customers' you entered is
mispelled or refers to a report that isn't open or doesn't exist.'

I'm Confused! Please help if you can!

Regards,

Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[MacDermott]

One possibility would be to change your RecordSource property in the
report's OnOpen event procedure, instead of doing it behind the calling
form.

There are other (mostly clumsy) ways to do this, but I generally find I can
use a parameterized query for the recordsource, referring to textboxes
(which may be hidden) on the calling form. That way I never have to change
the recordsource at all.

HTH
- Turtle

 

[Simon Harris]

Hi,

Thanks for your reply...you say that one possibility would be to to change
my RecordSource property in the
report's OnOpen event procedure. Is that how you do things?

And you mention an parameterized query - Do you mean you set the value of a
check box to a string of HTML, then do as above, using the onOpen event to
check then value of the textbox, setting this as the recordsource?

Sorry if I'm being dumb...slight amount of panic here due to a project
deadline that has just passed...and guess what, the works not finished! (Is
it ever!?)

Regards,

Simon.

 

[MacDermott]

I'm not sure what you mean with the question "Is that how you do things?"
It's one of the few times that the report is open (so you don't get "not in
Records collection"), and it hasn't run yet (so you don't get "Can't change
RecordSource after report has run).

Is this how I do it personally?
I've rarely had the need.

Most of the changes I've needed to make "on the fly" have been essentially
matters of filtering.
For example, if I have a RecordSource like this:
SELECT * FROM MyTable
But I want to be able to show only those records where MyField has a certain
(numerical) value, I can put that value into a textbox named MyText on
frmMyForm, and set the RecordSource like this:

SELECT * FROM MyTable WHERE MyField=Forms!frmMyForm!MyText

HTH
- Turtle

 

[Simon Harris]

Thanks again for your help...

When you say:
set the RecordSource like this:
SELECT * FROM MyTable WHERE MyField=Forms!frmMyForm!MyText

What event do you fire this from?

Regards,
Simon.

 

[MacDermott]

Set it in the RecordSource property of the Report at Design Time.
Then you don't have to change it -
just put different values in the textbox before running the report.

HTH
- Turtle

 

[Simon Harris]

Ok got it - Thanks!

Set it in the RecordSource property of the Report at Design Time.
Then you don't have to change it -
just put different values in the textbox before running the report.

HTH
- Turtle