Open a report based on which query I select

  • Thread starter Thread starter Sierras
  • Start date Start date
S

Sierras

Hello

I have a report which opens based on a query.
I would like to open the same report but based on a different query.
Can I make a command button (or two command buttons) which will allow
the user to choose which query they want to run and then open that one
report based on which query they picked?

Otherwise, I have to make many copies of the same report and base each
of them on their own individual queries and then create a command
button to launch each different report.

Thanks
 
Assuming that all the output fields are named the same. . . . you can set
the recordsource of the report to whichever query you wish the report to be
based on in the onopen event of the report

Me.RecordSource = "QueryA"

HTH
 
Yes - I know how to base the report to a query, this is what I have
now. But I would like to be able to have the user choose which query
the report is based on (kind of making the RecordSource dynamic). For
example:

I have two queries: Qurey 1 and Query 2
And one Report: Report A

When I click command button 1, I would like Query 1 to be the
RecordSource to Report A
And when I click command button 2, I would like Query 2 to be the
RecordSource to Report A

Is this possible?

Thanks
 
Yes - I know how to base the report to a query, this is what I have
now. But I would like to be able to have the user choose which query
the report is based on (kind of making the RecordSource dynamic). For
example:

I have two queries: Qurey 1 and Query 2
And one Report: Report A

When I click command button 1, I would like Query 1 to be the
RecordSource to Report A
And when I click command button 2, I would like Query 2 to be the
RecordSource to Report A

Is this possible?

Thanks
Click to expand...

If you insist upon 2 separate command buttons, you could use:
In one button:
DoCmd.OpenReport "ReportA", acViewPreview, "Query1"
In the other:
DoCmd.OpenReport "ReportA", acViewPreview, "Query2"

Or use just one command button.
Add an Option Group to the form with the 2 (or more) choices.
Query1 or Query2 or Query3, etc.
Then code a single command button:
DoCmd.OpenReport "ReportA", acViewPreview

Code the Report's Open Event:

If forms!FormName!OptionGroupName = 1 Then
Me.RecordSource = "Query1"
ElseIf forms!FormName!OptionGroupName = 2 Then
Me.RecordSource = "Query2"
Else
Me.RecordSource = "Query3"
End If

You could also use a Combo Box (or a List Box) on the form, with the
name of the query as text in the bound column, in which case you would
use:

Me.RecordSource = forms!FormName!ComboName

The form must remain open when the report is run.
 
Just wanted to let you know that I finally understood what you meant
by using the Record Source in the OPEN EVENT. I was using the record
source in the Data tab of the report.

So now I just made a combo box with all the different reports and then
on Open, the report uses the data in the combo box as its records
source. That works great and this way, I only have to change ONE
report if I want to format it differently instead of having to make
the same changes on multiple reports.

Not sure if you this is the best way. I created a table with all the
different query names and am using that table for the combo box. If
you got a better way, please let me know

Thanks again...
 
Thanks - I guess we were typing at the same time.
One more thing, do you know how I can put the name of the report in
the header of the report?
Right now, I can pass the text from the combo box to the report, but
that is the name of the query. I have the name of the query and the
name of the report in the same table. Is there anyway I can use the
name in the combo box and show the corresponding name of the report
which is in the table?
Have I confused you all??
 
In your combobox make sure that both columns are selected (qryname,
reportname) the reportname doesn't have to be visible.

on your report put a text box with a controlsourece like so

=Forms!form2.combo0.column(1)

Alter the elements to suit but remember that the column count is zero based

HTH
 
Thanks. I wish I would have just waited for your answer before trying
to figure it out myself.
I got it work, but very complicated. I had made an invisible text box
and put the info from the combo box column in it. Then on the report,
I called up the text box.
I like your way much better. So now, I deleted everything and just
added one line to the report with your suggestion.
Works great

Thanks again!!
 
Thanks - I guess we were typing at the same time.
One more thing, do you know how I can put the name of the report in
the header of the report?
Right now, I can pass the text from the combo box to the report, but
that is the name of the query. I have the name of the query and the
name of the report in the same table. Is there anyway I can use the
name in the combo box and show the corresponding name of the report
which is in the table?
Have I confused you all??
Click to expand...

To show the name of the report in the Report header, add an unbound
control.
Set it's control source to:
=[Name]
 
Back
Top