Multiple Queries-Want only 1 Report

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi there. Using A02 on XP. I have a report and 6 queries.
I open the report and print, then go to design view and
change the control source to the next query, run, print,
design view, change control source, run, print, etc. How
can I have one report and select which of 6 queries to run
it with? Using a list box to select the criteria won't
work as the queries have a few diff types of criteria like
one query has PlanNum like "6*" and another query has
PlanNum <"5000" AND StdInd = "S".

I've seen a few posts in the past but cannot recall how.
Please note that I am not a programmer, just a self taught
Access lover getting to know VB and event procedures. I
need something pretty basic. Can anyone give me a little
direction or advice? Thanks in advance for any help!
 
Bonnie said:
Hi there. Using A02 on XP. I have a report and 6 queries.
I open the report and print, then go to design view and
change the control source to the next query, run, print,
design view, change control source, run, print, etc. How
can I have one report and select which of 6 queries to run
it with? Using a list box to select the criteria won't
work as the queries have a few diff types of criteria like
one query has PlanNum like "6*" and another query has
PlanNum <"5000" AND StdInd = "S".

I've seen a few posts in the past but cannot recall how.
Please note that I am not a programmer, just a self taught
Access lover getting to know VB and event procedures. I
need something pretty basic. Can anyone give me a little
direction or advice? Thanks in advance for any help!
Click to expand...


If the differences in the query are all in the criteria,
then you could use just one query without these possisble
criteria. Use a command button on a form to open the report
and use the OpenReport method's WhereCondition argument to
specify the criteria. One button could use:

stCriteria = "PlanNum like '6*' "

Another could use:

stCriteria = "PlanNum <5000 AND StdInd = 'S' "

Or you could use text boxes on the form to allow the user to
enter the 5000 and/or the S and construct the criteria in
code:

stCriteria = "PlanNum < " & txtPlanNo & _
" AND StdInd = '" & txtStdInd & "'"

The report would be then be opened using a line like:

DoCmd.OpenReport "nameofreport", , ,stCriteria
 
Hi...Marsh??? I'm tapping my heels together and hoping
you still have a thread attached to this...

Just got a chance to use your reply but quickly found that
my 6 queries are structured differently (connected to
different subqueries) and so I have to ask this question:

Can I have a set of buttons and each one opens the same
report but with a different record source (one button for
each of the 6 queries)? Unless it's easier to do it a
different way. Each time I have to edit my report, it's a
lion, tiger or a bear to then flip out 5 more copies.

I'll keep tapping for awhile before posting regular.

There's no place like newsgroups, there's no place like
newsgroups, there's no place like newsgroups...
 
Bonnie said:
Hi...Marsh??? I'm tapping my heels together and hoping
you still have a thread attached to this...

Just got a chance to use your reply but quickly found that
my 6 queries are structured differently (connected to
different subqueries) and so I have to ask this question:

Can I have a set of buttons and each one opens the same
report but with a different record source (one button for
each of the 6 queries)? Unless it's easier to do it a
different way. Each time I have to edit my report, it's a
lion, tiger or a bear to then flip out 5 more copies.

I'll keep tapping for awhile before posting regular.

There's no place like newsgroups, there's no place like
newsgroups, there's no place like newsgroups...
Click to expand...

;-) but are you wearing your red shoes?

You can change the report record source query in the
report's Open event procedure.

The way I do this kind of thing is to have a hidden text box
in the form's header or footer. Then each button's click
event can set the text box to the appropriate query (either
an SQL statement or the name of a saved query):

Me.thetextbox = "reportquery1"

The report's Open event then copies the query from the form
to its own RecordSource property:

Me.RecordSource = Forms.yourform.thetextbox
 
Back
Top