Pass Parameter to a report from VBA

[Campbell C via AccessMonster.com]

Hi.

I have a report that the user can open and run. It prompts them for a
parameter, job_number, and then generates the report based on that. I also
need to be able to print this report from within another report. I can't do
it as a sub-query because of the page orientations. It only has to be
printed in certain conditions, which I am able to obtain in either the detail.
print or the detail.format events of the main report.

How can I open the second report without either prompting the user to enter
the parameter (which may not be immediately available for them) or creating
a duplicate report and/or query to use in this situation?

TIA

 

[Duane Hookom]

Remove the parameter prompt from the report's record source query. If you
want to open a report with a filter, use the Where clause in the
DoCmd.OpenReport method.

 

[Campbell C via AccessMonster.com]

Thanks Duane but as I tried to say ( not so clearly ) in my original post,
the parameter is required and must stay in place for when the user runs the
history report manually.
I need to also be able to run this history report for each record in a
checklist report that has a certain flag set. This must be all done
automatically behind the scenes.

Remove the parameter prompt from the report's record source query. If you
want to open a report with a filter, use the Where clause in the
DoCmd.OpenReport method.

Hi.

[quoted text clipped - 15 lines]

TIA

 

[Duane Hookom]

You don't understand. You should move away from running reports except from
code in a form or other. Is there some reason you don't want to open the
report with code that includes the where clause?

Adding criteria in a report's record source limits the usefulness of the
report.

--
Duane Hookom
MS Access MVP

Thanks Duane but as I tried to say ( not so clearly ) in my original post,
the parameter is required and must stay in place for when the user runs
the
history report manually.
I need to also be able to run this history report for each record in a
checklist report that has a certain flag set. This must be all done
automatically behind the scenes.

Remove the parameter prompt from the report's record source query. If you
want to open a report with a filter, use the Where clause in the
DoCmd.OpenReport method.

Hi.

[quoted text clipped - 15 lines]

TIA

 

[Campbell C via AccessMonster.com]

Sry Duane but I do understand exactly what you meant. However, the client
specifically needs the report to be able to be run manually and input the
parameter. I don't have a choice. So, I'm not able to change the existing
report. I simply (or not) have to run the same report from code and avoid
having it prompt the user for the parameter but instead using the parameter I
have access to in code.
The where clause remains the same in both cases. where wo.job_number = [JOB
NUMBER];
However, when run from code, it CAN NOT prompt the user for it. All I want
to do is pass the parameter in code to the report.

You don't understand. You should move away from running reports except from
code in a form or other. Is there some reason you don't want to open the
report with code that includes the where clause?

Adding criteria in a report's record source limits the usefulness of the
report.

Thanks Duane but as I tried to say ( not so clearly ) in my original post,
the parameter is required and must stay in place for when the user runs

[quoted text clipped - 13 lines]

 

[Duane Hookom]

I can't fathom why there would be a specification like "we must have a
report based on a parameter query".

I would then use code to change the SQL property of the saved query prior to
the report running. After the report has been run, change the SQL property
back to the parameter query.

--
Duane Hookom
MS Access MVP

Sry Duane but I do understand exactly what you meant. However, the client
specifically needs the report to be able to be run manually and input the
parameter. I don't have a choice. So, I'm not able to change the
existing
report. I simply (or not) have to run the same report from code and avoid
having it prompt the user for the parameter but instead using the
parameter I
have access to in code.
The where clause remains the same in both cases. where wo.job_number =
[JOB
NUMBER];
However, when run from code, it CAN NOT prompt the user for it. All I
want
to do is pass the parameter in code to the report.

You don't understand. You should move away from running reports except
from
code in a form or other. Is there some reason you don't want to open the
report with code that includes the where clause?

Adding criteria in a report's record source limits the usefulness of the
report.

Thanks Duane but as I tried to say ( not so clearly ) in my original
post,
the parameter is required and must stay in place for when the user runs

[quoted text clipped - 13 lines]

TIA