If you use the parameters that read the form, the form does have to stay
open until the report(s) are finished.
The alternative is to remove the criteria from the query, and have your
button's Click procedure create the WhereCondition to filter the report.
There's an example in Method 2 of this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
In Access 2002 or 2003, you can pass a description of the filter string in
the OpenArgs of OpenReport, and then display that expression on the report.
It's much nicer to read than the actual Filter string.
Since we generally design a form as the interface to the reports anyway,
this form can launch many report. Typically it has an option group or combo
where you select the report. It's AfterUpdate event shows whatever boxes
should be offered to the user to filter that report. The user enters any
criteria they need, and clicks your Print or Preview command button. The
code in those buttons builds the WhereCondition string, and the description,
and executes an OpenReport with the correct report, view, WhereCondition,
and OpenArgs.
The result is that one form launches all your reports, offering all the
criteria options the user could wish for, and giving them the chance to
preview or print.
There's a rather limited example of this approach in Northwind.
The form is called Sales Report Dialog.
There other aspect that you might want to be able to do is give the user the
option to specify one of their printers as one to use for that specific
report. You don't know what their printers will be when you write the
database, but you can create a custom property for the report so they can
assign a printer to it. If you want to do that also, see:
Printer Selection Utility
at:
http://allenbrowne.com/AppPrintMgt.html
Requires A2002 or later. Includes a wrapper function OpenTheReport() that
sets up the correct printer, accepts the WhereCondition, passes the
Description, handles the 2501 error if the report does not open, and returns
True or False so you can determine whether the open succeeded.
HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
SCM said:
Thanks, Allen. I understand your point. A few follow up questions then.
Will
the frmWhatDates form close automatically once I have entered my criteria?
If
I need to keep that form open in order pass the parameters it seems that
this
form will just remain open constantly. I don't want the user having to
figure
out how to handle that. My other problem is that there are multiple
reports
feeding off of the original underlying query. To program the OK cmd
button's
OnClick property will tie it to just one form. I don't want to open a
different report based on the query only to have the frmWhatDates form
launch
the graph report. How would I set up other reports to execute in the same
manner? Hope that makes sense.
Allen Browne said:
What we normally do is open the form, and have it open the report.
The Click event procedure of your command button would then work like
this:
Private Sub cmdOk_Click()
If (IsNull(Me.txtStartDate) OR IsNull(Me.txtEndDate) Then
Msgbox "Both dates please"
Else
DoCmd.OpenReport "Report1", acViewPreview
End If
End Sub
That's probably the best way to approach it. When you develop serious
applications, you don't let the users anywhere near the Database window,
so
all reports are opened from forms.
SCM said:
Thanks for the help, Allen. I accomplished what you said with one
exception;
I am not sure how to properly execute the sequence of 1) opening the
graph
report 2) having my frmWhatDates form open and 3)keep the frmWhatDates
open
but minimized so that the graph report takes the focus. What is
happening
now
is that the graph report opens without data feeding the graph, the
frmWhatDates opens and has the focus, and I don't get the intended
result.
I
set the OnOpen property of the graph report to a macro that opens the
frmWhatDates form. On the frmWhatDates form are "OK" and "Cancel"
command
buttons that minimize or close the form, respectively. When I remove
that
OnOpen property setting, the report opens and uses the "Enter
Parameter"
dialogue box prompts as you would expect. So the question is then, how
do
I
open the report, have it open the frmWhatDates form, pass the
parameters
to
the graph report, and display the graph report with data as intended?
thanks again,
SCM
:
The query that feeds the graph is not the RecordSource of the report.
The report does not have access to the parameters in the graph's
query.
So the graph and the report are able to read the same values, use a
form.
Create a form with 2 text boxes named (say) txtStartDate and
txtEndDate.
Set their Format property to Short Date, so Access knows how to handle
the
value and won't accept bad dates.
Save the form as frmWhatDates.
Now in the query that feeds your graph, use criteria of:
Between [Forms].[frmWhatDates].[txtStartDate]
And [Forms].[frmWhatDates].[txtEndDate]
In the report, you can now set the Control Source of your text box to:
="Between " & [Forms].[frmWhatDates].[txtStartDate] &
" And " & [Forms].[frmWhatDates].[txtEndDate]
Notes:
1. The form must stay open for this to work.
2. To ensure the parameters are understood correctly, declare them.
In query Design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms].[frmWhatDates].[txtStartDate] Date/Time
[Forms].[frmWhatDates].[txtEndDate] Date/Time
I am trying to generate a graph report in Access that displays the
underlying
query criteria on the report header. I have done this successfully
in
other
reports that are not "graph reports", but cannot get it done in a
report
generated with the Chart Wizard. My criteria are basically starting
and
ending dates. The expression that works on other reports is
="Between"
&
[Type the beginning date:]& " and "&[Type the ending date:]. I use
the
same
expression on my graph report in the Report Header and all I end up
with
is
the #Name? error in the text box. I've even tried stripping the
criteria
out
of the original query and re-entering it into the Select Query that
is
the
control source for the actual graph. Still get the #Name? error.
Thanks
for
any help with this.
