Enter Parameter Value error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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.
 
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]));











Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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.
Click to expand...
Click to expand...
 
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"

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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]));











Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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?

--
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
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?
Click to expand...
Click to expand...
Click to expand...
 
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));
Click to expand...
Click to expand...
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.




Duane Hookom said:
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"

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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]));











Duane Hookom said:
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.

--
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.


:

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

--
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
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?
Click to expand...
Click to expand...
Click to expand...
 
Do you have any code that closes the form?
Is there a typo with:
Forms!ChooseDate!StartDate
and
[Forms]![Choose Date]![StartDate]

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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));
Click to expand...
Click to expand...
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.




Duane Hookom said:
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"

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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
the entire SQL statement to paste into a reply.

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

--
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.


:

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

--
Duane Hookom
MS Access MVP
--

message
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?
Click to expand...
Click to expand...
Click to expand...
 
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.


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

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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));
Click to expand...
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.




Duane Hookom said:
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"

--
Duane Hookom
MS Access MVP
--

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
the entire SQL statement to paste into a reply.

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

--
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.


:

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

--
Duane Hookom
MS Access MVP
--

message
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?
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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


--
Duane Hookom
MS Access MVP
--

StephanieH said:
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.


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

--
Duane Hookom
MS Access MVP
--

StephanieH said:
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
Date". I don't see anything in your SQL that provides a column named
"Start
Date"

--
Duane Hookom
MS Access MVP
--

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
the entire SQL statement to paste into a reply.

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

--
Duane Hookom
MS Access MVP
--

message
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?

--
Duane Hookom
MS Access MVP
--

message
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?
Click to expand...
Click to expand...
Click to expand...
 
Yay! That was it.

Thank you soooooooo much!



Duane Hookom said:
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


--
Duane Hookom
MS Access MVP
--

StephanieH said:
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.


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

--
Duane Hookom
MS Access MVP
--

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
Date". I don't see anything in your SQL that provides a column named
"Start
Date"

--
Duane Hookom
MS Access MVP
--

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
the entire SQL statement to paste into a reply.

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

--
Duane Hookom
MS Access MVP
--

message
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?

--
Duane Hookom
MS Access MVP
--

message
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top