Assigning Different Record Sources to One Report

D

Denis

This problem should be fairly straightforward to resolve,
though I have not been able to find anything on it.

I have a report (MyRpt) with no RecordSource associated
with it (in the Design mode).

I want to open this report from 2 separate forms, and
based on 2 separate queries. i.e. When opening MyRpt from
frmOne, I want to assign qryOne as its Record Source; and
when opening MyRpt from frmTwo, I want to assign qryTwo as
its Record Source. Both queries contain the same fields;
the only difference is their criteria refer to an object
within their respective forms.

How is this achievable?

Thank you for your time.

Denis
 
D

Duane Hookom

You can assign the record source property in the On Open event of the
report.

I would probably either change the SQL property of one saved query or modify
one query to work.
 
M

Marshall Barton

Denis said:
This problem should be fairly straightforward to resolve,
though I have not been able to find anything on it.

I have a report (MyRpt) with no RecordSource associated
with it (in the Design mode).

I want to open this report from 2 separate forms, and
based on 2 separate queries. i.e. When opening MyRpt from
frmOne, I want to assign qryOne as its Record Source; and
when opening MyRpt from frmTwo, I want to assign qryTwo as
its Record Source. Both queries contain the same fields;
the only difference is their criteria refer to an object
within their respective forms.
Click to expand...


First, I'll give you a generalized answer to your question
and then I'll tell you that's not necessarily the right way
to go about it ;-)

You can set the report's RecordSource only in the report's
Open event procedure. There are various ways to make the
desired query name availbe to the report, depending on which
version of Access you're using. But, let's not get involved
with that, unless you really need it.

You can change the report to use the base table or a query
without the differing criteria. This way, if you open the
report directly from the database window, you get all the
data. To get the forms to filter the report, modify the
code behind the form's print button to use the OpenReport
method's WhereCondition argument something like this:
...
stCriteria = "thisfield = " & me.sometextbox
DoCmd.OpenReport stDocName, acViewPreview, _
WhereCondition:= stCriteria

The other form can set the stCriteria variable according to
its needs. This way, the report has no need to be aware of
any of this stuff.
 
D

Denis

Wayne and Marsh,
I suspected there was a better and certainly a more
efficient way of achieving this, but I couldn't figure it
out.
Thank you for your help.
Denis
 
E

Emily8

I have a basic report that I can use for a number of different queries. How
do you set the record source? It seems silly to create 14 different reports
for 14 different queries, when the report is the exact same.

I.e. rpt_FullListing can use any of the following queries (the user is
selecting the report from a menu); qry_Area, qry_AreaBYStatus, qry_LicenseNum

Each of these queries are from the same table(s), but use different criteria
based on which report the user wants.
 
R

Rick Brandt

Emily8 said:
I have a basic report that I can use for a number of different queries. How
do you set the record source? It seems silly to create 14 different reports
for 14 different queries, when the report is the exact same.

I.e. rpt_FullListing can use any of the following queries (the user is
selecting the report from a menu); qry_Area, qry_AreaBYStatus, qry_LicenseNum

Each of these queries are from the same table(s), but use different criteria
based on which report the user wants.
Click to expand...

In most cases you could use a single query that returns all possible
records and just apply a filter when opening the report by using the WHERE
argument of DoCmd.OpenReport. Have you tried that?

Otherwise you can change the RecordSource property of the Report in its
Open event.
 
E

Emily8

Can you do that when you have the user entering criteria themselves?

This is what the one query looks like (well, not quite, it has about 15
different fields:

SELECT PIPELINE_PIPELINE_DETAILS.OWNER,
PIPELINE_PIPELINE_DETAILS.LICENSE_NUM, PIPELINE_PIPELINE_DETAILS.LINE_NUM,
PIPELINE_PIPELINE_DETAILS.AREA, PIPELINE_PIPELINE_DETAILS.SERVICE,
PIPELINE_PIPELINE_DETAILS.LINE_TYPE, PIPELINE_PIPELINE_DETAILS.PRODUCT,
PIPELINE_PIPELINE_DETAILS.STATUS
FROM PIPELINE_PIPELINE_DETAILS
WHERE (((PIPELINE_PIPELINE_DETAILS.AREA) Like (UCase([Enter Area] & "*"))));
 
D

Duane Hookom

I rarely place dynamic filtering in queries that are the recordsources for
main reports. I prefer to use form controls for values to filter a report.
For instance a form with a text boxes for begin and end dates might have a
button with code to open a report

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [SaleDate]>=#" & _
Me.txtStartDate & "# "
End IF
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [SaleDate]<=#" & _
Me.txtEndDate & "# "
End IF
DoCmd.OpenReport "rptPipelineDetails", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Emily8 said:
Can you do that when you have the user entering criteria themselves?

This is what the one query looks like (well, not quite, it has about 15
different fields:

SELECT PIPELINE_PIPELINE_DETAILS.OWNER,
PIPELINE_PIPELINE_DETAILS.LICENSE_NUM, PIPELINE_PIPELINE_DETAILS.LINE_NUM,
PIPELINE_PIPELINE_DETAILS.AREA, PIPELINE_PIPELINE_DETAILS.SERVICE,
PIPELINE_PIPELINE_DETAILS.LINE_TYPE, PIPELINE_PIPELINE_DETAILS.PRODUCT,
PIPELINE_PIPELINE_DETAILS.STATUS
FROM PIPELINE_PIPELINE_DETAILS
WHERE (((PIPELINE_PIPELINE_DETAILS.AREA) Like (UCase([Enter Area] &
"*"))));


Rick Brandt said:
In most cases you could use a single query that returns all possible
records and just apply a filter when opening the report by using the
WHERE
argument of DoCmd.OpenReport. Have you tried that?

Otherwise you can change the RecordSource property of the Report in its
Open event.
Click to expand...
Click to expand...
 
Top