Report Questions

[Guest]

I created a report which has in its open event a call to a form that
asks for date range. The form has date from and to. It also has OK &
Cancel buttons.
Once I hit OK (with valid dates) I change the report recordsource and
add the date range criteria. The report then displays fine.. 2
Questions...

1 ) If I hit cancel on the form, the report still opens.. I tried
adding code under the cancel command and say DoCmd.Close report... but
it did not allow me doing so


2) I want to show on the report the date range that was selected. But
when I refere to the form date from and to, it says it does not exist,
which is correct, since I close the form after I open the report.. Is
there a way I can capture the date from and to for display?

 

[Guest]

Is it really necessary to close the form? Unless you are using 2003, there
would be no way to pass the info from the form to the report (sort of, I can
suggest a work around).

If you are on 2003, you could use the OpenArgs argument in the OpenReport
method and pass the dates to the report; otherwise, there are two options.
One is to hide the form if you don't want it to be seen, and reference the
dates on the form. Another way would be to create two static functions in a
standard module. This is a little trick I use from time to time. Create a
static function. To put a value into it, call it with a date. It will then
retain that date and pass it back to you. If you call it with no argument,
then it will return the last value it was passed. Because it is in a
standard module, it will retain it's value as long as your app is open.
Create one each of these for From Date and To Date. Then call them in the
after update event of their respective controls in the form:

FromDate(Me.txtFromDate)

Then in the report, you can call them with no argument and it will return
the date you entered in the form:

Me.FromDate = FromDate()

Here is how the function looks:

Static Function FromDate(Optional varFDate As Variant) As Variant
Dim varSaveDate As Variant

If Not IsMissing(varFDate) Then
varSaveDate = varFDate
End If
FromDate = varSaveDate
End Function

 

[Duncan Bachen]

I created a report which has in its open event a call to a form that
asks for date range. The form has date from and to. It also has OK &
Cancel buttons.
Once I hit OK (with valid dates) I change the report recordsource and
add the date range criteria. The report then displays fine.. 2
Questions...

1 ) If I hit cancel on the form, the report still opens.. I tried
adding code under the cancel command and say DoCmd.Close report... but
it did not allow me doing so


2) I want to show on the report the date range that was selected. But
when I refere to the form date from and to, it says it does not exist,
which is correct, since I close the form after I open the report.. Is
there a way I can capture the date from and to for display?

Rather than opening a form from within the report itself, would you
consider opening the form first?

This way you can prompt the user for the date range and have a command
button which prints the report.

In turn, the report's underlying query can refer to the open form for
it's date range criteria AND the report can also refer to the form's
text boxes for the date range so it can display them in the report.

This avoids having to reset the datasource and close the report the way
you are currently doing it. You simply don't run the report if the date
values are blank.

Make sense?