Adding subreport results in multiple Enter Parameter Value prompts

[josephwalter]

i have 2 reports that run fine on their own, but when i put one in the footer of the other (as a subreport), access promptes me 4 times to the same value

Query1 is the record source of Report1
"Enter Report Date" is a parameter to Query1
Query2 is built on Query1, and is the record source for Report2

when I open Report1 i am prompted once for "Enter Report Date". when I open Report2, i am prompted once for "Enter Report Date".

the problem is when i put Report2 in the report footer section of Report1. then i open Report1 and get 4 prompts for "Enter Report Date". (I've discovered that i can leave the value blank on the 2nd and 3rd prompt and the report still run fine).

why is Access asking me for the same value? how can i surpress these?

 

[drkhelmt]

Just a guess...

It sounds like you are prompted when the Report1 opens, due to Query1 running. Since you are prompted 4 times (I'm guessing) Report1 has 3 rows, so that when Report2 is in the footer section, after every row, Report2 is run which in turn runs Quwery1 prompting you for the date.

Try hardcoding the a date in Query1 and see what materializes.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[josephwalter]

Report2 is in the Report Footer (not a section footer).

Report1 has 127 rows of data to display. I've got 1 level of grouping (which splits the 127 rows in about a dozen groups).

Hard coding results in no prompts for the parameter value. I use the parameter value in two places in Query1, so hard coding led me to this experiment:

Leaving one hard-coded and the other as [Enter Report Date], I get all 4 prompts for the parameter value (regardless of which one is hard-coded).

Access must be running my query 4 times to generate my report... but that seems excessive.

 

[drkhelmt]

I just thought of this...

I kind of assumed you have the master/child of the reports linked with that date field. Is this correct?

If that doesn't solve the problem, I'm going to have to bow out. I have no idea.

Hope this gets you closer to the solution.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[josephwalter]

I (intentionally) left the linked fields blank on the subreport control. So, no, I don't have the master/child of the reports linked with the date field.

Report2 shows the same data as Report1, just summarized differently. (one is by category by employee, the other is simply an employee summary w/ no category info)

Here's a solution that involves too much work (but would get it working... in case someone else finds this thread and Must Have a Solution):

1. build a form that looks like the Enter Parameter Value form
2. have the queries pick the date off this form
3. open the form before opening the report. when user clicks OK on the form, it hides the form (visible=false)
4. the report opens, picking the param value off the form as often as it needs it
5. in the OnClose event of the report, close the form

i've done this before for other reasons, but i don't like creating extra objects/code because Access is misbehaving. i'd rather just put together my queries/reports correctly, and i'm obviously doing something wrong on this one...