changing report's record source

[Ed Cohen]

I am using Access 2000 and I need to change the record source for a report.
Unfortunately, I have never been able to do this. According to the help file,
you should be able to do this and it looks quite simple. Well, I cannot even
access the report. I have tried the following methods:

Reports!rptTopCaseClubMembersV2.RecordSource = "some query"
Reports![rptTopCaseClubMembersV2].RecordSource = "some query"
Reports("rptTopCaseClubMembersV2").RecordSource = "some query"

None of these methods work. All I get is an error saying that it cannot find
the report and it does not exist. I just created the report! What is
happening here? Also, when accessing forms, I get the same problems. The only
way I can access a form or report to set a property is to do this:

Report_rptTopCaseClubMembersV2.RecordSource = "some query". Then when I run
that code I get another error saying that you cannot set this property after
the report has been printed. I have not even issued the DoCmd.OpenReport
command yet.

Please Help

Ed Cohen

 

[Pat Hartman]

Doing it externally, the report already needs to be open to be part of the
Reports collection or you need to open the report in design view before
changing the RecordSource. Both of these methods cause problems. I change
the recordsource within the report itself in the report's Open Event. There
are various ways to communicate to the report which query to use. One way
is to send either the querydef name or the actual SQL string in the OpenArgs
when you use the OpenReport method to open the report.

 

[Ed Cohen]

Doing it externally, the report already needs to be open to be part of the
Reports collection or you need to open the report in design view before
changing the RecordSource. Both of these methods cause problems. I change
the recordsource within the report itself in the report's Open Event. There
are various ways to communicate to the report which query to use. One way
is to send either the querydef name or the actual SQL string in the OpenArgs
when you use the OpenReport method to open the report.

I am using Access 2000 and I need to change the record source for a report.
Unfortunately, I have never been able to do this. According to the help
file,
you should be able to do this and it looks quite simple. Well, I cannot
even
access the report. I have tried the following methods:

Reports!rptTopCaseClubMembersV2.RecordSource = "some query"
Reports![rptTopCaseClubMembersV2].RecordSource = "some query"
Reports("rptTopCaseClubMembersV2").RecordSource = "some query"

None of these methods work. All I get is an error saying that it cannot
find
the report and it does not exist. I just created the report! What is
happening here? Also, when accessing forms, I get the same problems. The
only
way I can access a form or report to set a property is to do this:

Report_rptTopCaseClubMembersV2.RecordSource = "some query". Then when I
run
that code I get another error saying that you cannot set this property
after
the report has been printed. I have not even issued the DoCmd.OpenReport
command yet.

Please Help

Ed Cohen

Thank you for your response. But there is no open args argument on the
DoCmd.OpenReport. There is only a filtername argument, which according to the
help file can be the name of a query. Well, I tried it and the report reverts
to the original query set for it.

Ed cohen

 

[Tom Wickerath]

Hi Ed,

I think Pat simply mentioned that using OpenArgs is one method, not
necessarily the method you implemented. I also do this change in the Reports
Open event procedure. Access MVP Armen Stein has a sample for Access 2000,
which you can download. See "Report Selection Techniques", here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

His sample changes the report's recordsource by modifying the WHERE clause
of a SQL string. This is done by opening a form in dialog mode, so that the
report's open event procedure is suspended, until the dialog form is hidden.
I would think it would be just as easy to change to a saved querydef, but
I've never tried that myself.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pat Hartman]

If you are using an older version of Access, you may not have the OpenArgs
argument. I don't remember which version added it - maybe XP. Tom pointed
you to another technique that will accomplish what you need. If you don't
want to embed the form process in the report itself, you can make a form
that allows users to build their selection criteria and then run the report.
In the report's Open event, the report would get its RecordSource
information from a field on the criteria form.

If the criteria isn't variable, just use the Where argument when you open
the report. This is the simplest method. You can change the value based on
what the client selects. For example the criteria statement might look
like:

"StudentID = " & Forms!yourcriteriaform!cboStudentID

This assumes that the criteria is always StudentID but the value can change
at runtime. This is my most frequent situation.

Doing it externally, the report already needs to be open to be part of
the
Reports collection or you need to open the report in design view before
changing the RecordSource. Both of these methods cause problems. I
change
the recordsource within the report itself in the report's Open Event.
There
are various ways to communicate to the report which query to use. One
way
is to send either the querydef name or the actual SQL string in the
OpenArgs
when you use the OpenReport method to open the report.

I am using Access 2000 and I need to change the record source for a
report.
Unfortunately, I have never been able to do this. According to the help
file,
you should be able to do this and it looks quite simple. Well, I cannot
even
access the report. I have tried the following methods:

Reports!rptTopCaseClubMembersV2.RecordSource = "some query"
Reports![rptTopCaseClubMembersV2].RecordSource = "some query"
Reports("rptTopCaseClubMembersV2").RecordSource = "some query"

None of these methods work. All I get is an error saying that it cannot
find
the report and it does not exist. I just created the report! What is
happening here? Also, when accessing forms, I get the same problems.
The
only
way I can access a form or report to set a property is to do this:

Report_rptTopCaseClubMembersV2.RecordSource = "some query". Then when I
run
that code I get another error saying that you cannot set this property
after
the report has been printed. I have not even issued the
DoCmd.OpenReport
command yet.

Please Help

Ed Cohen

Thank you for your response. But there is no open args argument on the
DoCmd.OpenReport. There is only a filtername argument, which according to
the
help file can be the name of a query. Well, I tried it and the report
reverts
to the original query set for it.

Ed cohen

 

[Ed Cohen]

If you are using an older version of Access, you may not have the OpenArgs
argument. I don't remember which version added it - maybe XP. Tom pointed
you to another technique that will accomplish what you need. If you don't
want to embed the form process in the report itself, you can make a form
that allows users to build their selection criteria and then run the report.
In the report's Open event, the report would get its RecordSource
information from a field on the criteria form.

If the criteria isn't variable, just use the Where argument when you open
the report. This is the simplest method. You can change the value based on
what the client selects. For example the criteria statement might look
like:

"StudentID = " & Forms!yourcriteriaform!cboStudentID

This assumes that the criteria is always StudentID but the value can change
at runtime. This is my most frequent situation.

Doing it externally, the report already needs to be open to be part of
the
Reports collection or you need to open the report in design view before
changing the RecordSource. Both of these methods cause problems. I
change
the recordsource within the report itself in the report's Open Event.
There
are various ways to communicate to the report which query to use. One
way
is to send either the querydef name or the actual SQL string in the
OpenArgs
when you use the OpenReport method to open the report.

I am using Access 2000 and I need to change the record source for a
report.
Unfortunately, I have never been able to do this. According to the help
file,
you should be able to do this and it looks quite simple. Well, I cannot
even
access the report. I have tried the following methods:

Reports!rptTopCaseClubMembersV2.RecordSource = "some query"
Reports![rptTopCaseClubMembersV2].RecordSource = "some query"
Reports("rptTopCaseClubMembersV2").RecordSource = "some query"

None of these methods work. All I get is an error saying that it cannot
find
the report and it does not exist. I just created the report! What is
happening here? Also, when accessing forms, I get the same problems.
The
only
way I can access a form or report to set a property is to do this:

Report_rptTopCaseClubMembersV2.RecordSource = "some query". Then when I
run
that code I get another error saying that you cannot set this property
after
the report has been printed. I have not even issued the
DoCmd.OpenReport
command yet.

Please Help

Ed Cohen

Thank you for your response. But there is no open args argument on the
DoCmd.OpenReport. There is only a filtername argument, which according to
the
help file can be the name of a query. Well, I tried it and the report
reverts
to the original query set for it.

Ed cohen

Pat,
Thanks for the tip. I went to that web site and had success using their
technique. Then I simply did the same thing but popped up the report. In the
report, I grabbed the sql string I stored in a hidden field on the form.
Works like a champ. Thanks to everyone who helped.

Ed Cohen