subreport problem

[deltauser2006]

Hey, I currently have multiple reports which pull data from one set of
tables. Every quarter the tables are updated and the old data is
stored in a different set of historical tables and is tagged with the
appropriate quarter and year that they are from. I am trying to create
the same reports except have them reflect the historical data. In
otherwords, when the report is opened, I want the user to pick a
quarter and year and then have the report produce information for that
quarter and year from the historical tables. The problem is, when I do
this, for every subreport in the report I am prompted to enter the
quarter and year over again. This means entering quarter and year 25
different times. Instead I would like to have to do this only once.
Please let me know if you have any ideas about how to fix it so quarter
and year only have to be entered once for both report and all
subreports. Thanks!

 

[Steve Schapel]

DeltaUser,

Instead of a parameter query where the user is prompted to enter the
quarter and year criteria, consider putting a couple of unbound
textboxes or comboboxes on a form where the user can enter the criteria.
Then, in the criteria of your queries, refer to these controls using
syntax such as [Forms]![NameOfForm]![NameOfControl]

Do you mean you have a report with 25 subreports? Gosh!

 

[Allen Browne]

The simplest way to do this would be to leave the data in the original
tables, and use some kind of flag (yes/no field? date field?) to indicate if
it is an archive record or should be considered as belonging to another
period. That solves the problem completely. It is easy enough to create a
query that filters out all the archive records, and use that query where
ever you are currently using your table.

Another option would be to create a form with some unbound controls that let
you specify the criteria for the queries of the main report and subreport.
The criteria would look like this:
[Forms].[Form1].[Text0]
Since the queries are reading the criteria from the form, they don't have to
keep asking you for the same value.

If the data must be collected from multiple tables, you might need to read
up on UNION queries.

If the query statement for the subreport needs to be re-written, it is
possible to do so in code. Before you OpenReport:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

Hope that gets you out of trouble. The first suggestion is the simplest,
most powerful, most flexible, and easiest to maintain.