Subreport problem, how to avoid beeing askes for the same parameter several times

[Hallgeir]

How can I use the same parameter in main and subreport without beeing asked
for the parameters several times.

I have a subreport in my report that use the same parameter as the main
report: "Between [From date:] and [To date:]. When I run the report it asks
me for "From date" and "To date" several times. Is there a way that I can
avoid this.

Thanks!

 

[Allen Browne]

Create a little unbound form, with 2 text boxes named (say) txtStartDate and
txtEndDate. Set the Format property of both boxes to Short Date so Access
knows they are dates. Add a command button to open the report if you wish.
Save the form with a name such as Form1.

Open your query in design view.
Change the criteria to:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

Now the query can read the dates from the form, instead of bothering you
multiple times for the same dates.

Just to ensure the query interprets the dates correctly, it is advisable to
declare the parameters. In query design, choose Parameters on the Query
menu. Access opens a dialog. Enter 2 rows:
[Forms].[Form1].[txtStartDate] Date
[Forms].[Form1].[txtEndDate] Date

 

[Van T. Dinh]

Set up your database such that when the use wants to run the
Report/SubReport combination, your database opens a Form [frmParameter]
which has 2 TextBoxes ( and / or Calendar Controls) for the user to enter
the FromDate value and ToDate value and a CommandButton to actually open the
Report/SubReport combination (check valid dates are entered before the
OpenReport statement!).

In the RecordSources for the Report/SubReports, use the references to the 2
TextBoxes on the Form [frmParameter] as the Parameters for the Queries.

 

[Hallgeir]

I was hopeing that there was a way to avoid using a form, but apparently a
form is the best solution for this problem.
Thank you for your response.