Automating a parameter query

[Guest]

I have a parameter query that has 15 possible responses which is then the
basis for a report. We want the report run 15 times (one time for each
parameter query response). Is there any way to automate the responses so the
user doesn't have to type in the criteria each time? I tried including the
macro actions: openquery and sendkeys, but sendkeys runs after openquery has
run.

If VBA is an alternative, I know it somewhat.

Thanks for any help!

 

[John Vinson]

I have a parameter query that has 15 possible responses which is then the
basis for a report. We want the report run 15 times (one time for each
parameter query response). Is there any way to automate the responses so the
user doesn't have to type in the criteria each time? I tried including the
macro actions: openquery and sendkeys, but sendkeys runs after openquery has
run.

If VBA is an alternative, I know it somewhat.

Thanks for any help!

Is this a one-shot operation? If so, I'd suggest simply removing the
criterion.

If it will be ongoing, then consider changing the criterion from

[enter parameter]

to

[enter parameter] OR [enter parameter] IS NULL

to retrieve all records if the user types nothing into the parameter.

John W. Vinson[MVP]

 

[Guest]

Thanks--this was helpful.

To better explain what I'm doing: I need to create 15 separate reports (on
an on-going basis). The report layout is the same, only the data
changes--usually it's just a different division--so I'm inputting the
division codes in the parameter query to create the reports. I'd rather just
create a macro that simulates this sequence since these reports need to be
generated regularly. I just discovered that the SendKeys action types in
values if you put it before the openquery action. If I could simulate
pressing the Enter key, then I would have solved this with a macro. Can you
simulate pressing the Enter key with the SendKeys action?

If not, then I have a complicated parameter query that perhaps you can help
me streamline. If it was simply 15 divisions that needed to be prompted then
your suggestion of: "[Enter division code] is null" is all I would need.

For several of the divisions, however, they also want a certain department
excluded from that division, but then that department then gets its own
report.

I have it working in a complicated set of four parameter prompts. The first
line looks like this: (Under the division code field) Like "*" & [Enter
Division code to include]
(Under the department code field) Not Like "*" & [Enter Department to exclude]

This gets me the special divisions that exclude a department.

Then on the next Division code criteria line down (OR line), I have:
[Enter Division code]
to get the normal divisions (all departments)

Then on the next Department code criteria line down (OR line), I have:
[Enter Department code]
to get the previously excluded department all by itself.

This actually gets me my data (believe it or not), but as you can imagine,
it's very confusing. I couldn't get the is null to work with this kind of
request.

if you have any suggestions, I would really appreciate it.

Thanks again!

I have a parameter query that has 15 possible responses which is then the
basis for a report. We want the report run 15 times (one time for each
parameter query response). Is there any way to automate the responses so the
user doesn't have to type in the criteria each time? I tried including the
macro actions: openquery and sendkeys, but sendkeys runs after openquery has
run.

If VBA is an alternative, I know it somewhat.

Thanks for any help!

Is this a one-shot operation? If so, I'd suggest simply removing the
criterion.

If it will be ongoing, then consider changing the criterion from

[enter parameter]

to

[enter parameter] OR [enter parameter] IS NULL

to retrieve all records if the user types nothing into the parameter.

John W. Vinson[MVP]

 

[Van T. Dinh]

* Remove the Parameter from the Query.

* Run the OpenReport 15 times, each time with a different
"wherecondition"/"filter" argument for the OpenReport.

* Depending on how you store these "parameter values", you may be able to
use a loop in your code to execute a single OpenReport statement 15 times
with different "wherecondition" eaxh time.

Check Access VB Help on OpenReport for the arguments.

Alternatively, you can design 1 Report (using the Query without parameter)
with suitable Sorting and Grouping, Group Header/Footer, forced Page Break,
Page Header/Footer so that the each portion of the Report for a particular
Parameter value always starts on a new page.

HTH
Van T. Dinh
MVP (Access)

 

[John Vinson]

Thanks--this was helpful.

To better explain what I'm doing: I need to create 15 separate reports (on
an on-going basis).

Do they need to be *fifteen separate reports*, or *fifteen sheets of
paper*, which could all be one report with page breaks?

The report layout is the same, only the data
changes--usually it's just a different division--so I'm inputting the
division codes in the parameter query to create the reports. I'd rather just
create a macro that simulates this sequence since these reports need to be
generated regularly. I just discovered that the SendKeys action types in
values if you put it before the openquery action. If I could simulate
pressing the Enter key, then I would have solved this with a macro. Can you
simulate pressing the Enter key with the SendKeys action?

SendKeys is buggy, unreliable, and unnecessary. I'd really suggest
using VBA; you can loop in VBA (which you cannot in a macro), for one
thing.

If not, then I have a complicated parameter query that perhaps you can help
me streamline. If it was simply 15 divisions that needed to be prompted then
your suggestion of: "[Enter division code] is null" is all I would need.

For several of the divisions, however, they also want a certain department
excluded from that division, but then that department then gets its own
report.

I have it working in a complicated set of four parameter prompts. The first
line looks like this: (Under the division code field) Like "*" & [Enter
Division code to include]
(Under the department code field) Not Like "*" & [Enter Department to exclude]

This gets me the special divisions that exclude a department.

Then on the next Division code criteria line down (OR line), I have:
[Enter Division code]
to get the normal divisions (all departments)

Then on the next Department code criteria line down (OR line), I have:
[Enter Department code]
to get the previously excluded department all by itself.

I'm perplexed. Could you perhaps post the SQL view of this query? I
don't see how this is working!

This actually gets me my data (believe it or not), but as you can imagine,
it's very confusing. I couldn't get the is null to work with this kind of
request.

if you have any suggestions, I would really appreciate it.

After I see the SQL (and have some coffee tomorrow morning <g>) I
might be able to think of something - but a loop in VBA calling the
report multiple times sounds like a good bet.

John W. Vinson[MVP]

 

[Guest]

Place your parameter in a table. Add the table to your query without joining.
Use the field from the table as the criteria. In the report set the sorting
and grouping.

 

[Guest]

The OpenReport action accomplishes what I'm looking to do. Thank you SO much
for this suggestion!!! I'm overwhelmed at getting such quick help...I really
appreciate it!