Enter Parameter Value error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StephanieH - 29 Dec 2005 20:50 GMT

I created 3 separate reports all tied to the same form. I use this form to
enter the parameter values "Start Date" and "End Date". When using the form,
all 3 reports run fine.

Then, I decided to combine these reports into 1. I took report #1, made a
copy and changed the name. I used the form to access the new report and
everything ran fine. I added report #2 as a sub-report (no copies, no name
changes, just added through the toolbox wizard). When I went back to the
form to enter the dates, report #1 ran fine, but the subreport promts "Enter
Parameter Value - Start Date" and "Enter Parameter Value - End Date". I'm
lost. Why would the report run fine when accessed from the form, but the
same report prompt for a value when used as a subreport?

Reply to this Message

Duane Hookom - 29 Dec 2005 20:59 GMT

Could you share the SQL view of your report and subreport record sources?

Signature

Duane Hookom
MS Access MVP
--

>I created 3 separate reports all tied to the same form. I use this form to
> enter the parameter values "Start Date" and "End Date". When using the
[quoted text clipped - 12 lines]
> lost. Why would the report run fine when accessed from the form, but the
> same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 30 Dec 2005 13:09 GMT

Sure, just tell me how. I may have to remove the sensitive data, but I
should be able to share enough to figure out what's not working.

> Could you share the SQL view of your report and subreport record sources?
>
[quoted text clipped - 14 lines]
> > lost. Why would the report run fine when accessed from the form, but the
> > same report prompt for a value when used as a subreport?

Reply to this Message

Duane Hookom - 30 Dec 2005 14:39 GMT

Go to your report design view and find the Record Source property. Click the
builder [...] button to open the query design. View the SQL view and copy
the entire SQL statement to paste into a reply.

Then also provide us with the exact error message you are getting.

Signature

Duane Hookom
MS Access MVP
--

> Sure, just tell me how. I may have to remove the sensitive data, but I
> should be able to share enough to figure out what's not working.
[quoted text clipped - 23 lines]
>> > the
>> > same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 30 Dec 2005 15:05 GMT

Here's the SQL for the main report:

SELECT Atty_Placements.agency, Atty_Placements.[prev agency],
Atty_Placements.[loan type], Atty_Placements.account,
Atty_Placements.Balance, Atty_Placements.[date assigned],
Atty_Placements.Backout, Atty_Placements.[Backout Week],
Atty_Placements.[Backout Reason], Atty_Placements.InstructionalCode
FROM Atty_Placements
WHERE (((Atty_Placements.agency) Not Like "*HOURLY*") AND
((Atty_Placements.[date assigned]) Between [Forms]![Choose Date]![StartDate]
And [Forms]![Choose Date]![EndDate]) AND ((Atty_Placements.Backout)=No));

When I follow the same steps in my subreport, I see the options of "Name"
"Backouts from Current Week" and "Source" "Report.Backouts from Current
Week". There is no "Record Source" like for the main report.

If it helps, here's the SQL statement for my stand-alone report that used as
the subreport:
SELECT Atty_Placements.agency, Atty_Placements.[loan type],
Atty_Placements.account, Atty_Placements.Balance, Atty_Placements.[date
assigned], Atty_Placements.InstructionalCode, Atty_Placements.Backout,
Atty_Placements.[Backout Week], Atty_Placements.[Backout Reason]
FROM Atty_Placements
WHERE (((Atty_Placements.[date assigned]) Between [Forms]![Choose
Date]![StartDate] And [Forms]![Choose Date]![EndDate]) AND
((Atty_Placements.Backout)=Yes) AND ((Atty_Placements.[Backout Week]) Between
[Forms]![Choose Date]![StartDate] And [Forms]![Choose Date]![EndDate]));

> Go to your report design view and find the Record Source property. Click the
> builder [...] button to open the query design. View the SQL view and copy
[quoted text clipped - 29 lines]
> >> > the
> >> > same report prompt for a value when used as a subreport?

Reply to this Message

Duane Hookom - 30 Dec 2005 15:20 GMT

And what are your exact error messages? Your earlier statement "Enter
Parameter Value - Start Date" suggested the report needed a value for "Start
Date". I don't see anything in your SQL that provides a column named "Start
Date"

Signature

Duane Hookom
MS Access MVP
--

> Here's the SQL for the main report:
>
[quoted text clipped - 69 lines]
>> >> > the
>> >> > same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 30 Dec 2005 15:43 GMT

Sorry, the error message is "Enter Parameter Value"
"Forms!ChooseDate!StartDate"

I'm using a form to select the dates. The main report (which pulls
correctly) has the SQL WHERE (((Atty_Placements.agency) Not Like "*HOURLY*")
AND

> > ((Atty_Placements.[date assigned]) Between [Forms]![Choose
> > Date]![StartDate]
> > And [Forms]![Choose Date]![EndDate]) AND ((Atty_Placements.Backout)=No));

which is the information I specified in the query for that report.

But the subreport doesn't seem to refer back to the query I used to create
it before I inserted it as a subreport.

I don't understand why the original would be linked to the original query,
but once entered as a subreport, it would no longer see the reference to the
query.

> And what are your exact error messages? Your earlier statement "Enter
> Parameter Value - Start Date" suggested the report needed a value for "Start
[quoted text clipped - 74 lines]
> >> >> > the
> >> >> > same report prompt for a value when used as a subreport?

Reply to this Message

Duane Hookom - 30 Dec 2005 17:40 GMT

Do you have any code that closes the form?
Is there a typo with:
Forms!ChooseDate!StartDate
and
[Forms]![Choose Date]![StartDate]

Signature

Duane Hookom
MS Access MVP
--

> Sorry, the error message is "Enter Parameter Value"
> "Forms!ChooseDate!StartDate"
[quoted text clipped - 110 lines]
>> >> >> > the
>> >> >> > same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 30 Dec 2005 18:48 GMT

Here is the code from my form:

Private Sub coRunAll_Click()
On Error GoTo Err_coRunAll_Click
Dim stDocName As String
stDocName = "Wkly Placements and Backouts"
DoCmd.OpenReport "Wkly Placements and Backouts", acPreview
Exit_coRunAll_Click:
DoCmd.Close acForm, "Choose Date"
Exit Sub
Err_coRunAll_Click:
MsgBox Err.Description
Resume Exit_coRunAll_Click
DoCmd.Close acForm, "Choose Date"

End Sub

This was generated by the Command Wizard except that I added the DoCmd.Close
acForm, "Choose Date" to close the form after the report was opened.

In the form, I have two combo boxes. The first has a label named "Start
Date" and a Combo Box named "StartDate". The second has a lable named "End
Date" and a Combo Box names "EndDate".

The main report filters my records by the Start and End dates entered into
the form, but the dates don't make it all the way to the subreport.

> Do you have any code that closes the form?
> Is there a typo with:
[quoted text clipped - 116 lines]
> >> >> >> > the
> >> >> >> > same report prompt for a value when used as a subreport?

Reply to this Message

Duane Hookom - 30 Dec 2005 19:36 GMT

The issue is that you are closing the form so the subform can't find the
values in the controls. Don't close the form until the report is closed. You
can make it invisible. Try this code:

Private Sub coRunAll_Click()
On Error GoTo Err_coRunAll_Click
Dim stDocName As String
stDocName = "Wkly Placements and Backouts"
DoCmd.OpenReport "Wkly Placements and Backouts", acPreview
Exit_coRunAll_Click:
Me.Visible = False
Exit Sub
Err_coRunAll_Click:
MsgBox Err.Description
Resume Exit_coRunAll_Click
DoCmd.Close acForm, "Choose Date"

End Sub

Signature

Duane Hookom
MS Access MVP
--

> Here is the code from my form:
>
[quoted text clipped - 155 lines]
>> >> >> >> > the
>> >> >> >> > same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 03 Jan 2006 18:51 GMT

Yay! That was it.

Thank you soooooooo much!

> The issue is that you are closing the form so the subform can't find the
> values in the controls. Don't close the form until the report is closed. You
[quoted text clipped - 174 lines]
> >> >> >> >> > the
> >> >> >> >> > same report prompt for a value when used as a subreport?

Reply to this Message

StephanieH - 30 Dec 2005 18:58 GMT

Duane, our department is getting ready to close for the day, but I'll be back
on Monday. Thanks for you help so far. I hope you'll continue to help me
next week.

Happy New Years to everyone.

> I created 3 separate reports all tied to the same form. I use this form to
> enter the parameter values "Start Date" and "End Date". When using the form,
[quoted text clipped - 8 lines]
> lost. Why would the report run fine when accessed from the form, but the
> same report prompt for a value when used as a subreport?

Reply to this Message