It appears the problem lies in the query. I changed the criterion as you
instructed and on entering the start and end dates the report opens
without
the parameter request; however the report does not contain any data.
Here is the criterion I had inserted originally:
=[forms]![Report Date Range]![Start Date] And <=[forms]![Report Date
Range]![End Date]
With this I got data in the report, but had the parameter request.
With the original criterion, I opened the form, entered the dates, then
ran
the query and got the parameter request. I did the same thing with
criterion
you provided and the query ran without the parameter request, but did not
pull data from the table.
Here is code for the unbound field on the report.
Private Sub Report_Close()
DoCmd.Close acForm, "Report Date Range"
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "rptReferralsV1"
If Not CurrentProject.AllForms("Report Date Range").IsLoaded Then
Cancel = True
End If
End Sub
Also, on the form I originally had this code:
Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub
After one of the revisions we made, I got an error and debug highlited
this
code. I just deleted and the error msg cleared. Maybe I caused another
problem when I deleted this code.
The complete original form code is futher down in the dialog.
I appreciate the time and effort you have given!
Ken Snell said:
In the query, is the criterion expression that is reading the end date
value
looking like this:
[Forms]![Report Date Range]![End Date]
If not, change it to the above.
If it does, then my first guess is that you've misspelled the form name
or
the control name in the query's criterion expression. However, based on
the
code excerpts that you've posted, that doesn't appear to be the case.
The parameter request could also be coming from the report itself -- a
control on the report may be using the form's control's value in a
control
source? See if you can identify whether it's the query or the report
that is
prompting the parameter window to show -- you can do this if you open
the
form itself directly from the database window, enter start and end
dates,
and then open the query (the one that the report uses) from the database
window and see if you get the parameter.
--
Ken Snell
<MS ACCESS MVP>
Yes Sir! That did it. Thanks!
However, I still have something mixed up. I run the report and the
form
pops up. I enter the dates and click Preview and I get a box:
Enter Parameter Value Forms!Report Date Range!End Date and that is a
place to enter date. I enter the End Date and the report runs.
So I suppose I have something wrong in the form code. Here is form
code:
Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([Start Date]) Or IsNull([End Date]) Then
MsgBox "You must enter both Start and End dates."
DoCmd.GoToControl "Start Date"
Else
If [Start Date] > [End Date] Then
MsgBox "End date must be greater than Start Date."
DoCmd.GoToControl "Start Date"
Else
Me.Visible = False
End If
End If
End Sub
Would you be so kind as to heip me resolve this also? Many thanks
:
Sorry... it appears that I misread the original code. Try this:
If Not CurrentProject.AllForms("Report Date Range").IsLoaded Then
--
Ken Snell
<MS ACCESS MVP>
Thanks for your response. I revised per your instruction and it
worked;i.e.,
the form opened and accepted dates, but when I clicked PreviewI got
error
Msg:
Runtime error 2467
The expression you entered refers to an object that is closed or
doesn't
exist
I clicked debug and it highlited the following code line, the one I
modified
If Not CurrentProject.AllReports("Report Date Range").IsLoaded Then
The only advantage I see in this procedure is that if user does not
enter
an
end date that is later than the start date a prompt appears. So
this
is
not
something for which I have dire need. But if you see an easy fix
I'll
try
it.
Thanks again, your response may help others as well.
:
For ACCESS 2002 and higher version(s), there is an IsLoaded
property
for
CurrentProject.AllReports object... perhaps this is what was meant
to
be
used?
If Not CurrentProject.AllReports("Report Date Range").IsLoaded
Then
--
Ken Snell
<MS ACCESS MVP>
message
There's no build-in function called IsLoaded (AccessObject
objects
have
an
IsLoaded property, but that is not what is being used here).
It's
probably
a custom function in a module in the template. To fix the
problem,
find
and import the module, or copy and paste the function into one
of
your
own
standard modules.
--
Brendan Reynolds (MVP)
In one of the MS ACCESS templates, the developer created a form
Report
Date
Range for Start and End dates and for the form, a report and
its
underlying
query, has code to call the form for entering the date range
for
generating
the report. The report has an unbound field for this and there
is
code
in
the criteria of date field on the query. The form also has a
command
button
with code to Preview the report.
I am trying to use what he or she did with not much success so
far.
I
entered the code as is replacing only the name of the report
with
my
own.
Here is code for the unbound field:
Private Sub Report_Close()
DoCmd.Close acForm, "Report Date Range"
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog,
"rptReferralsV1"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub
When I run the report it goes to the code and IsLoaded is
highlighted
with
this error Msg: Compile error Sub or Function not defined.
What is wrong?
Thanks
