Multiple queries using 1 report

[Guest]

I have several different queries returning different summaries but all with
the same format (Year, Business, Department, Q1TD, Q2TD, Q3TD, Q4TD, YTD,
....).

Is there a way to choose which query to use with a report?
Ex.:
Run Report1 for query1
Run Report1 for query2
Run Report1 for query3
Run Report2 for query1
etc...

Thanks

Mauricio Silva

 

[Duane Hookom]

You can set the report's record source in the on open event of the report. I
kinda question why you have several saved queries that might just have
different "hard-coded" criteria.

 

[Guest]

Actualy, they don't have different criteria but they are from different
tables which have the same structure and must be reported in the same way.

I my question, I didn't explain correctly what I am trying to do. What I am
having problems with is to change the SubReport record source. I have tryed
this:

Me.RecordSource = "Query1"
SubReport1.Report.RecordSource = "SubQuery1"

and it didn't work, saying that I could not do it.

Even the workaround : Forms("SubReport1").RecordSource = "SubQuery1"
didn't work.

It looks like that at OnLoad event time, the subreports are not loaded yet.

Thanks

Mauricio Silva

 

[Marshall Barton]

I'll leave it to Duane to wonder why you have multiple
tables when Relational Database Normalization rules clearly
state that there should only be one table.

As for changing a subreport's record source, this is a
little tricky because it must be done in the subreport's
Open event procedure. BUT it can only be done once in the
first occurance of the subreport in the main report. To
achieve this you can use code like this:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Query1"
Initialized = True
End If

 

[Guest]

Thank you very much Marshall ...

Duane... do not stress yourself wondering why I am trying to do this because
I didn't explain evething. But, if you wish I can explain better....

Take care

Mauricio Silva

I'll leave it to Duane to wonder why you have multiple
tables when Relational Database Normalization rules clearly
state that there should only be one table.

As for changing a subreport's record source, this is a
little tricky because it must be done in the subreport's
Open event procedure. BUT it can only be done once in the
first occurance of the subreport in the main report. To
achieve this you can use code like this:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Query1"
Initialized = True
End If
--
Marsh
MVP [MS Access]

Actualy, they don't have different criteria but they are from different
tables which have the same structure and must be reported in the same way.

I my question, I didn't explain correctly what I am trying to do. What I am
having problems with is to change the SubReport record source. I have tryed
this:

Me.RecordSource = "Query1"
SubReport1.Report.RecordSource = "SubQuery1"

and it didn't work, saying that I could not do it.

Even the workaround : Forms("SubReport1").RecordSource = "SubQuery1"
didn't work.

It looks like that at OnLoad event time, the subreports are not loaded yet.

 

[Duane Hookom]

I'm not going to lose any sleep over this since I don't have to work with
the database ;-)

--
Duane Hookom
MS Access MVP

Thank you very much Marshall ...

Duane... do not stress yourself wondering why I am trying to do this because
I didn't explain evething. But, if you wish I can explain better....

Take care

Mauricio Silva

I'll leave it to Duane to wonder why you have multiple
tables when Relational Database Normalization rules clearly
state that there should only be one table.

As for changing a subreport's record source, this is a
little tricky because it must be done in the subreport's
Open event procedure. BUT it can only be done once in the
first occurance of the subreport in the main report. To
achieve this you can use code like this:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Query1"
Initialized = True
End If
--
Marsh
MVP [MS Access]

Actualy, they don't have different criteria but they are from different
tables which have the same structure and must be reported in the same way.

I my question, I didn't explain correctly what I am trying to do. What I am
having problems with is to change the SubReport record source. I have tryed
this:

Me.RecordSource = "Query1"
SubReport1.Report.RecordSource = "SubQuery1"

and it didn't work, saying that I could not do it.

Even the workaround : Forms("SubReport1").RecordSource = "SubQuery1"
didn't work.

It looks like that at OnLoad event time, the subreports are not loaded yet.

:
You can set the report's record source in the on open event of the report. I
kinda question why you have several saved queries that might just have
different "hard-coded" criteria.

"Mauricio Silva" wrote
I have several different queries returning different summaries but all
with
the same format (Year, Business, Department, Q1TD, Q2TD, Q3TD, Q4TD, YTD,
...).

Is there a way to choose which query to use with a report?
Ex.:
Run Report1 for query1
Run Report1 for query2
Run Report1 for query3
Run Report2 for query1
etc...

 

[Guest]

I have a similar problem. I am have several queries that are in the same
format with different saved criteria. The criteria is complex and I want to
be able to save the queries once I get them "just right" but want to choose
which query is used with one of several reports. All of the reports are
based on the same input template so that all the queries should be compatible
with all the reports. I am trying to do this through a form where the user
can select the desired query and match it to the desired report - with no
luck! I can get the selected report to open but cannot get the Record Source
to recognize the selected query on the form.

Any assistance would be GREATLY appreciated. I am somewhat new at this, so
please be very specific...

My form uses 2 combo boxes: cboQueryList and cboReportList

The combo boxes are working - they show the correct information. How do I
change the record source on the selected report?

Thanks.

Susan

I'll leave it to Duane to wonder why you have multiple
tables when Relational Database Normalization rules clearly
state that there should only be one table.

As for changing a subreport's record source, this is a
little tricky because it must be done in the subreport's
Open event procedure. BUT it can only be done once in the
first occurance of the subreport in the main report. To
achieve this you can use code like this:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Query1"
Initialized = True
End If
--
Marsh
MVP [MS Access]

Actualy, they don't have different criteria but they are from different
tables which have the same structure and must be reported in the same way.

I my question, I didn't explain correctly what I am trying to do. What I am
having problems with is to change the SubReport record source. I have tryed
this:

Me.RecordSource = "Query1"
SubReport1.Report.RecordSource = "SubQuery1"

and it didn't work, saying that I could not do it.

Even the workaround : Forms("SubReport1").RecordSource = "SubQuery1"
didn't work.

It looks like that at OnLoad event time, the subreports are not loaded yet.

 

[Larry Linson]

The combo boxes are working - they show
the correct information. How do I change
the record source on the selected report?

You can change the Record Source of a Report in the Open Event of the
Report. You can refer back to the QueryName combo box on the selection Form,
if you leave that form open after you initiate the DoCmd.OpenReport
statement. If you are using Access 2002 or 2003, you can pass that Query
name to the Report in the "OpenArgs" property of the DoCmd.OpenReport.

Larry Linson
Microsoft Access MVP

 

[Guest]

I tried to do this but I am not getting the syntax correctly. Could you show
me some code please? I am leaving the form open when the report launches and
I am using Access 2003. I am not very familiar with using the OpenArgs
function. Please show me! I want to open [cboQueryList].Value.* as the
record source...

Please respond - I have posted three different questions, and I get great
early response with zero follow up. Thanks in advance.

Susan

 

[Larry Linson]

I tried to do this but I am not getting
the syntax correctly. Could you show
me some code please? I am leaving the
form open when the report launches and
I am using Access 2003. I am not very
familiar with using the OpenArgs
function. Please show me! I want to
open [cboQueryList].Value.* as the
record source...

I assume that you place your DoCmd.OpenReport statement in the Click event
of a CommandButton, so it will be executed when you click the Command
Button. You should test the Controls to make sure something has been
selected in each Combo Box, which testing is included in the code below:

Dim stDocName As String
Dim strOpenArgs As String
If Not IsNull(Me.cboReportList) Then
stDocName = Me.cboReportList
If Not IsNull(Me.cboQueryList) Then
strOpenArgs = Me.cboQueryList
DoCmd.OpenReport stDocName, acPreview, , , , strOpenArgs
Else
MsgBox "Query must be specified, please try again."
End If
MsgBox "Report must be specified, please try again."
End If

In addition, in the Open event of the Report, the following VBA code will
use the name of the Query, passed in OpenArgs, to reset the RecordSource of
the Report. If you have a great deal of data, you may see some evidence that
this approach is more efficient than using the WhereCondition or the Filter
arguments of DoCmd.OpenReport.

Generally, Report contents are so closely bound to the RecordSource that the
only thing that can differ is to select a different subset of records from a
larger source. If that is true with your report, as it likely is, consider
reading about and using the WhereCondition or Filter arguments instead of
making separate Queries. In my test database, where I used the Northwind.MDB
Category table as my test data, I could just as, or more, easily selected a
Category in my Combo box and constructed a WhereCondition argument that
would have worked equally as well.

Please respond - I have posted three
different questions, and I get great
early response with zero follow up.
Thanks in advance.

All the answers in these newsgroups, with extremely rare exceptions, are
from volunteers. And, I expect that most have schedules similar to mine --
participation in newsgroups will vary from day to day, because other
activities may take priority. In fact, from the time I began this response
and the time I finished it, I had a five-hour interruption.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

I really appreciate you taking the time to look at this for me and your
reply. I understand that all the help is from volunteers - the problem for
me has been that I get one response (the first one) and no follow up
responses. I eventually answered my own questions in the first two cases and
eventually will post some tips for future readers on those threads. Feel
free to check them - last time I looked there was no activity (and it has
been at least three weeks since my last post).

I tried your code (which as actually very similar to the code I had tried)
and got the same error message:

syntax error in FROM clause

The debugger goes to the "Do.Cmd" line. I cannot find the problem. Any
additional tips would be greatly appreciated.

The reports have the "OpenArgs" text box on them. What, if anything, should
be on the report itself under the RecordSource? I have tried leaving it
blank (which brings up the correct report with no data, just "NAME?" in all
the fields). FYI - after the correct report launches, it also shows your
pop-up error message telling me to choose a report.

Your response mentioned :

In addition, in the Open event of the Report, the following VBA code will
use the name of the Query, passed in OpenArgs, to reset the RecordSource of
the Report.

Did I miss the code? I think this is my problem. The record source is not
being reset. The queries I developed are based on using two multi-select
list boxes that have been pared down by a prior multi-select box. I can run
the report without any problem when the user initially makes these choices
(by setting a filter in the Where condition) - I followed Allen Browne's
example. However, the user wants to be able to use the query for external
applications or to run it again at a later date without rebuilding it - hence
the need to save the query. Anyway, I found I way to save the query, now I
just want an easy way to use the saved query to launch a report.

Thanks in advance.

Susan

I tried to do this but I am not getting
the syntax correctly. Could you show
me some code please? I am leaving the
form open when the report launches and
I am using Access 2003. I am not very
familiar with using the OpenArgs
function. Please show me! I want to
open [cboQueryList].Value.* as the
record source...

I assume that you place your DoCmd.OpenReport statement in the Click event
of a CommandButton, so it will be executed when you click the Command
Button. You should test the Controls to make sure something has been
selected in each Combo Box, which testing is included in the code below:

Dim stDocName As String
Dim strOpenArgs As String
If Not IsNull(Me.cboReportList) Then
stDocName = Me.cboReportList
If Not IsNull(Me.cboQueryList) Then
strOpenArgs = Me.cboQueryList
DoCmd.OpenReport stDocName, acPreview, , , , strOpenArgs
Else
MsgBox "Query must be specified, please try again."
End If
MsgBox "Report must be specified, please try again."
End If

In addition, in the Open event of the Report, the following VBA code will
use the name of the Query, passed in OpenArgs, to reset the RecordSource of
the Report. If you have a great deal of data, you may see some evidence that
this approach is more efficient than using the WhereCondition or the Filter
arguments of DoCmd.OpenReport.

Generally, Report contents are so closely bound to the RecordSource that the
only thing that can differ is to select a different subset of records from a
larger source. If that is true with your report, as it likely is, consider
reading about and using the WhereCondition or Filter arguments instead of
making separate Queries. In my test database, where I used the Northwind.MDB
Category table as my test data, I could just as, or more, easily selected a
Category in my Combo box and constructed a WhereCondition argument that
would have worked equally as well.

Please respond - I have posted three
different questions, and I get great
early response with zero follow up.
Thanks in advance.

All the answers in these newsgroups, with extremely rare exceptions, are
from volunteers. And, I expect that most have schedules similar to mine --
participation in newsgroups will vary from day to day, because other
activities may take priority. In fact, from the time I began this response
and the time I finished it, I had a five-hour interruption.

Larry Linson
Microsoft Access MVP