Etiquette Question - Calling reports from multiple locations

[NOSPAMrclark]

I'm wondering . . . .

What is the generally accepted method for calling reports from multiple
locations?

Example:

REPORT-A is called by selecting a record in a listbox and clicking a
button. The queries behind the record use the listbox value (selected)
as criteria to limit/build the report data.

Now, suppose I want to call that same report from a different form and
use the value of a text box as the criteria to limit the report data,
rather than the listbox selection.

Currently, I have a second set of queries that are limited using the
second criteria and a second report that calls those queries.

Seems like a very cumbersome way to accomplish this.

Anyone have a better way?

[pietlinden]

one way, and perhaps the most flexible, is to build the report without
any filters. then you can pass the filter you want from anywhere. So
if you call it from FormA, you can build the filter there (same as a
Where clause without the Where keyword), and then pass the filter in
the open event of the report.

This is especially useful when you have something like a multi-select
listbox to limit the contents of your report, since you have to loop
through the .ItemsSelected collection in code anyway. Once you have
that filter, you can just pass it as usual to the report's open method.

[SrPcAlarkM]

That will work if the report is a simple, single source report (one
query or table).

The problem I am running into is that I have this:

REPORT-A is based on QUERY-A
There are 3 sub-reports on the form.
SUBREPORT-A is based on QUERY-B which is a subset of QUERY-A
SUBREPORT-B is based on QUERY-C which is a different subset of QUERY-A
SUBREPORT-C is based on QUERY-D which is yet another subset of QUERY-A

If I call REPORT-A from a macro and use the Where Condition to limit,
the REPORT-A is limited to only the records I need, but all the
subreports arent limited since the Where Condition doesnt apply to
them. I therefore get all records in the three subs no matter which
record I'm limited to in the main report.

I'm thinking maybe I can use some value from REPORT-A to limit the
queries that feed data to the sub-reports but I cant seem to get that
to work.

Was hoping I was missing something simple.

Thanks for the reply. If you have any further thoughts, please pass
them on.

[SrPcAlarkM]

Ooops. I mean there are 3 sub-reports on the REPORT. Not on the form.

[bruce]

Sr********@azorinc.com wrote:

That will work if the report is a simple, single source report (one
query or table).

The problem I am running into is that I have this:

REPORT-A is based on QUERY-A
There are 3 sub-reports on the form.
SUBREPORT-A is based on QUERY-B which is a subset of QUERY-A
SUBREPORT-B is based on QUERY-C which is a different subset of QUERY-A SUBREPORT-C is based on QUERY-D which is yet another subset of QUERY-A
If I call REPORT-A from a macro and use the Where Condition to limit,
the REPORT-A is limited to only the records I need, but all the
subreports arent limited since the Where Condition doesnt apply to
them. I therefore get all records in the three subs no matter which
record I'm limited to in the main report.

I'm thinking maybe I can use some value from REPORT-A to limit the
queries that feed data to the sub-reports but I cant seem to get that
to work.

Was hoping I was missing something simple.

Thanks for the reply. If you have any further thoughts, please pass
them on.

If your main report and subreports cannot be redesigned such that the
linking field(s) between them all enforces your limiting criteria, you
could possibly use the main report's open event to set the filter
property of each subreport, e.g.

Private Sub Report_Open(Cancel As Integer)

Me!MySubreportA.Report.Filter=Me.Filter
Me!MySubreportB.Report.Filter=Me.Filter
' etc.

End Sub

HTH,
Bruce

[David W. Fenton]

"NO**********@azorinc.com" <rc****@azorinc.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:

What is the generally accepted method for calling reports from
multiple locations?

Example:

REPORT-A is called by selecting a record in a listbox and clicking
a button. The queries behind the record use the listbox value
(selected) as criteria to limit/build the report data.

Now, suppose I want to call that same report from a different form
and use the value of a text box as the criteria to limit the
report data, rather than the listbox selection.

Currently, I have a second set of queries that are limited using
the second criteria and a second report that calls those queries.

Seems like a very cumbersome way to accomplish this.

Anyone have a better way?

In this kind of situation, I often use a class module to store the
values and retrieve them in the OnOpen event of the report and
assign the report's recordsource there.

That way, you can use any form you want to collect the criteria.

I do, however, try to utilize as few criteria collection dialogs as
possible, which means occasionally having them dynamically show/hide
controls. But I would still usually use a class module as a data
storage structure so the report has to interact only with an
instance of that class module.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[SrPcAlarkM]

Thanks for all the input. This is still driving me nutts though.

Here is some more info.

Basically, I have a query that returns 5 records from my database. The
5 records would be similar to this.

Serial# Part# Order#
10001 B35 7
10002 B20 7
10003 B20 7
10004 B35 7
10005 B20 7

The report is a label for the shipping container and it will need the
following information.

1. The Order Information (Order Number, Shipping Info, etc). The main
source of data for the report is the Customer and Order Information,
which is pulled from a different database than the data above. (Hands
are tied on the table layout. Its from a 3rd party database)

2. Subform #1 has a table with the 5 records above listed. A shipment
detail, printed in a small table.

3. Subform #2 has the beginning and ending serial numbers, which I get
by using a Min/Max query on the first query. These are printed in big
block letters.

4. Subform #3 has a shipment summary table similar to the following.

Part# Qty
B20 3
B35 2

So, the form itself filters off the order number specified. In this
case, it will only show the customer and order info for order 7. The
first query has to limit off of the order # as well and the 2nd and 3rd
queries have to query the results of the first query.

If I set the criteria for the first query anywhere but in the form
calling it, the 2nd and 3rd queries wont work!

I HAVE to be missing something simple. This CANNOT be this hard! I'm
thinking that my thinking has thinking too much and I've grown a brain
bubble. I'm going to walk away for a few days and clear out some
cobwebs.

Any additional thoughts would be greatly appreciated.