A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter Prompt Annoyingly Appears (sometimes)



 
 
Thread Tools Display Modes
  #1  
Old April 4th, 2008, 12:41 AM posted to microsoft.public.access.reports
JeffP->
external usenet poster
 
Posts: 29
Default Parameter Prompt Annoyingly Appears (sometimes)

I have a form that I set the SQL query to either

if len(sParam) = 0 then
sSQL = "Year=2007"
Works fine I get all records for that year

If len(sParam) 0 then
sSQL = "Year=2007" & " and " & sParam
(value= "Year=2007 and salesrep = 'RosieS'")

Now I'm prompted to enter a parameter for salesrep - where does that come
from?

My report has a query that returns all records - no parameters or other
criteria.

Query... only criteria is that the job ID's exist...

SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job
Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job
Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt],
[Main Job Log].[SalesRep], [T_Year].[Year]
FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job
#]=[T_Year].[IDEnd]) AND ([Main Job Log].[Job #]=[T_Year].[IDStart])
ORDER BY [Main Job Log].[Job #];
....end query

...frmReport snipit....
sReportName = sRptName
sWhereFieldName = Me.lbl_WhereFieldName.Caption
sWhereFieldValue = Me.cbo_RptOptionList.Value

If Me.cbo_Year.Visible = True Then
sYear = "Year=" & Me.cbo_Year.Value
Else: sYear = ""
End If

If Me.cbo_RptOptionList.Value = "_All" Then
sWhere = ""
Else:
sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'"
End If

Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2))
Case 3
sQry = sYear & " and " & sWhere
Case 2
sQry = sWhere
Case 1
sQry = sYear
Case 0
sQry = sWhere
End Select

DoCmd.OpenReport sReportName, acViewPreview, , sQry
....end snipit....
  #2  
Old April 4th, 2008, 05:00 AM posted to microsoft.public.access.reports
Allen Browne
external usenet poster
 
Posts: 11,706
Default Parameter Prompt Annoyingly Appears (sometimes)

Try running the report's query on its own.
Does it ask for the parameter?
YES: Then problem is in the query.
a) Is the field really called SalesRep?
Or is it different, e.g. [Sales Rep]
b) Anything in the query's Filter or Order By properties?
(Properties box in query design.)

NO: Then problem is in the report.
a) Open the rpeort in design view, and clear its Filter and OrderBy
properties.
b) Look for controls that refer to the field wrongly (e.g. with the space.)
c) Look in the Sorting And Grouping box, to see if the field is wrong there.
d) If other fields on the report refer to SalesRep, but there is no text box
named SalesRep, try adding a text box with that name and bound to that
field. (Sometimes the Access report optimizer tries to be too clever.)

If you are still stuck, after opening the report, open the Immediate Window
(Ctrl+G), and ask it what the report's filter is, e.g.:
? Reports![Report1].Filter

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JeffP-" wrote in message
...
I have a form that I set the SQL query to either

if len(sParam) = 0 then
sSQL = "Year=2007"
Works fine I get all records for that year

If len(sParam) 0 then
sSQL = "Year=2007" & " and " & sParam
(value= "Year=2007 and salesrep = 'RosieS'")

Now I'm prompted to enter a parameter for salesrep - where does that come
from?

My report has a query that returns all records - no parameters or other
criteria.

Query... only criteria is that the job ID's exist...

SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job
Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job
Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt],
[Main Job Log].[SalesRep], [T_Year].[Year]
FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job
#]=[T_Year].[IDEnd]) AND ([Main Job Log].[Job #]=[T_Year].[IDStart])
ORDER BY [Main Job Log].[Job #];
...end query

..frmReport snipit....
sReportName = sRptName
sWhereFieldName = Me.lbl_WhereFieldName.Caption
sWhereFieldValue = Me.cbo_RptOptionList.Value

If Me.cbo_Year.Visible = True Then
sYear = "Year=" & Me.cbo_Year.Value
Else: sYear = ""
End If

If Me.cbo_RptOptionList.Value = "_All" Then
sWhere = ""
Else:
sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'"
End If

Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2))
Case 3
sQry = sYear & " and " & sWhere
Case 2
sQry = sWhere
Case 1
sQry = sYear
Case 0
sQry = sWhere
End Select

DoCmd.OpenReport sReportName, acViewPreview, , sQry
...end snipit....


  #3  
Old April 4th, 2008, 07:01 AM posted to microsoft.public.access.reports
JeffP->
external usenet poster
 
Posts: 29
Default Parameter Prompt Annoyingly Appears (sometimes)

Thanks for your reply, see my inline... I may post a followup as to my
solution or continued horrors....

"Allen Browne" wrote:

Try running the report's query on its own.
Does it ask for the parameter?

No, it quickly opens the entire table; note that if I choose my date as year
and the _All, then the where is only ..."where year=2007" and I'm not promted
the report runs and returns all the data for 2007, it's only when I add to
the where as, ..."where year=2007 and SalesRep = 'RosieS'" that I'm now
promted, the same if I choose manufacturer or any other field.

YES: Then problem is in the query.
a) Is the field really called SalesRep?

No, as you can see from the query it's SalesRep, as noted manufacture has
the same result, prompt to enter a parameter dialog, and of course the report
fails to run.

Or is it different, e.g. [Sales Rep]
b) Anything in the query's Filter or Order By properties?

None, there is nothing, further this report was working for manufacturer, it
was during the addition of adding SalesRep in the where SQL string that this
began.

(Properties box in query design.)

NO: Then problem is in the report.
a) Open the rpeort in design view, and clear its Filter and OrderBy
properties.

Filter is empty and order value = No

b) Look for controls that refer to the field wrongly (e.g. with the space.)

The controls get the fieldname and value from lookups, the form's cbo
fieldname and lookup list are dynamic similar to the Access Switchboard.

c) Look in the Sorting And Grouping box, to see if the field is wrong there.

None

d) If other fields on the report refer to SalesRep, but there is no text box
named SalesRep, try adding a text box with that name and bound to that
field. (Sometimes the Access report optimizer tries to be too clever.)

Yes, there is something here that is trying to be too clever. The form when
launched from different swichboard controls refrences a different item in my
reports table, which indicate the target form, report name, query for the
combo and the lable for the combo.

If you are still stuck, after opening the report, open the Immediate Window
(Ctrl+G), and ask it what the report's filter is, e.g.:
? Reports![Report1].Filter

That's a good step, I was trying to think of how I could trap that moment
because no matter what I enter for a parameter value the report fails, unless
I choose nothing as my sql where string - see my snipit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JeffP-" wrote in message
...
I have a form that I set the SQL query to either

if len(sParam) = 0 then
sSQL = "Year=2007"
Works fine I get all records for that year

If len(sParam) 0 then
sSQL = "Year=2007" & " and " & sParam
(value= "Year=2007 and salesrep = 'RosieS'")

Now I'm prompted to enter a parameter for salesrep - where does that come
from?

My report has a query that returns all records - no parameters or other
criteria.

Query... only criteria is that the job ID's exist...

SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job
Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job
Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt],
[Main Job Log].[SalesRep], [T_Year].[Year]
FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job
#]=[T_Year].[IDEnd]) AND ([Main Job Log].[Job #]=[T_Year].[IDStart])
ORDER BY [Main Job Log].[Job #];
...end query

..frmReport snipit....
sReportName = sRptName
sWhereFieldName = Me.lbl_WhereFieldName.Caption
sWhereFieldValue = Me.cbo_RptOptionList.Value

If Me.cbo_Year.Visible = True Then
sYear = "Year=" & Me.cbo_Year.Value
Else: sYear = ""
End If

If Me.cbo_RptOptionList.Value = "_All" Then
sWhere = ""
Else:
sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'"
End If

Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2))
Case 3
sQry = sYear & " and " & sWhere
Case 2
sQry = sWhere
Case 1
sQry = sYear
Case 0
sQry = sWhere
End Select

DoCmd.OpenReport sReportName, acViewPreview, , sQry
...end snipit....



  #4  
Old April 5th, 2008, 09:03 PM posted to microsoft.public.access.reports
NetworkTrade
external usenet poster
 
Posts: 825
Default Parameter Prompt Annoyingly Appears (sometimes)

I think you may have misinterpreted AB's advice.

You are getting a parameter prompt upon the opening of a report. There can
only be 2 reasons; the query (that is the source of the report) or the
report

If you open the query standalone and it does not have a parameter prompt....
then it is not the query.

This means that in the report there are controls (fields) in the report -
that can not find any data field in the underlying query when the report
attempts to open... This is not uncommon if someone redesigns to underlying
query but forgets to delete old controls from the report.

Also this same logic is valid for subreports and their queries as well....

That parameter prompt is telling you which field is being sought ...and that
is a helpful hint....
--
NTC


"JeffP-" wrote:

Thanks for your reply, see my inline... I may post a followup as to my
solution or continued horrors....

"Allen Browne" wrote:

Try running the report's query on its own.
Does it ask for the parameter?

No, it quickly opens the entire table; note that if I choose my date as year
and the _All, then the where is only ..."where year=2007" and I'm not promted
the report runs and returns all the data for 2007, it's only when I add to
the where as, ..."where year=2007 and SalesRep = 'RosieS'" that I'm now
promted, the same if I choose manufacturer or any other field.

YES: Then problem is in the query.
a) Is the field really called SalesRep?

No, as you can see from the query it's SalesRep, as noted manufacture has
the same result, prompt to enter a parameter dialog, and of course the report
fails to run.

Or is it different, e.g. [Sales Rep]
b) Anything in the query's Filter or Order By properties?

None, there is nothing, further this report was working for manufacturer, it
was during the addition of adding SalesRep in the where SQL string that this
began.

(Properties box in query design.)

NO: Then problem is in the report.
a) Open the rpeort in design view, and clear its Filter and OrderBy
properties.

Filter is empty and order value = No

b) Look for controls that refer to the field wrongly (e.g. with the space.)

The controls get the fieldname and value from lookups, the form's cbo
fieldname and lookup list are dynamic similar to the Access Switchboard.

c) Look in the Sorting And Grouping box, to see if the field is wrong there.

None

d) If other fields on the report refer to SalesRep, but there is no text box
named SalesRep, try adding a text box with that name and bound to that
field. (Sometimes the Access report optimizer tries to be too clever.)

Yes, there is something here that is trying to be too clever. The form when
launched from different swichboard controls refrences a different item in my
reports table, which indicate the target form, report name, query for the
combo and the lable for the combo.

If you are still stuck, after opening the report, open the Immediate Window
(Ctrl+G), and ask it what the report's filter is, e.g.:
? Reports![Report1].Filter

That's a good step, I was trying to think of how I could trap that moment
because no matter what I enter for a parameter value the report fails, unless
I choose nothing as my sql where string - see my snipit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JeffP-" wrote in message
...
I have a form that I set the SQL query to either

if len(sParam) = 0 then
sSQL = "Year=2007"
Works fine I get all records for that year

If len(sParam) 0 then
sSQL = "Year=2007" & " and " & sParam
(value= "Year=2007 and salesrep = 'RosieS'")

Now I'm prompted to enter a parameter for salesrep - where does that come
from?

My report has a query that returns all records - no parameters or other
criteria.

Query... only criteria is that the job ID's exist...

SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job
Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job
Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt],
[Main Job Log].[SalesRep], [T_Year].[Year]
FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job
#]=[T_Year].[IDEnd]) AND ([Main Job Log].[Job #]=[T_Year].[IDStart])
ORDER BY [Main Job Log].[Job #];
...end query

..frmReport snipit....
sReportName = sRptName
sWhereFieldName = Me.lbl_WhereFieldName.Caption
sWhereFieldValue = Me.cbo_RptOptionList.Value

If Me.cbo_Year.Visible = True Then
sYear = "Year=" & Me.cbo_Year.Value
Else: sYear = ""
End If

If Me.cbo_RptOptionList.Value = "_All" Then
sWhere = ""
Else:
sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'"
End If

Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2))
Case 3
sQry = sYear & " and " & sWhere
Case 2
sQry = sWhere
Case 1
sQry = sYear
Case 0
sQry = sWhere
End Select

DoCmd.OpenReport sReportName, acViewPreview, , sQry
...end snipit....



  #5  
Old May 21st, 2010, 10:45 AM posted to microsoft.public.access.reports
mal moores
external usenet poster
 
Posts: 1
Default parameter prompt

This thread saved my sanity!

I had a union query based on a number of other queries, all ran fine and returned the correct data.
My form and subform showed all the correct data and functioned perfectly.

When I ran the report based on the same query and the data I could see on screen I got the dreaded parameter prompt!!!

Although the above pointed to the report being the problem I even ended up rewriting the queries etc.
After two days and a lot of ~#~~~#**~~~ etc. I found this thread :0) thank god!!!
Two minutes later all working fine no parameter prompt.

SOLUTION:
Very last line on the screen in grouping/sorting was the word "Expression"? I clicked and expanded and there it was the **##***#~~~*!!!!! sort on "ATRecyID" I couldn't even remember adding this line, my data was sorted in the queries it wasn't required - deleated and away we go, perfect.

To be honest it should have been "NRRecyID" but it wasn't needed!

Hope this may help your sanity!

AB to the rescue again :0)




NetworkTrad wrote:

I think you may have misinterpreted AB's advice.
05-Apr-08

I think you may have misinterpreted AB's advice.

You are getting a parameter prompt upon the opening of a report. There can
only be 2 reasons; the query (that is the source of the report) or the
report

If you open the query standalone and it does not have a parameter prompt....
then it is not the query.

This means that in the report there are controls (fields) in the report -
that can not find any data field in the underlying query when the report
attempts to open... This is not uncommon if someone redesigns to underlying
query but forgets to delete old controls from the report.

Also this same logic is valid for subreports and their queries as well....

That parameter prompt is telling you which field is being sought ...and that
is a helpful hint....
--
NTC


"JeffP-" wrote:

Previous Posts In This Thread:

On Thursday, April 03, 2008 7:41 PM
Jeff wrote:

Parameter Prompt Annoyingly Appears (sometimes)
I have a form that I set the SQL query to either

if len(sParam) = 0 then
sSQL = "Year=2007"
Works fine I get all records for that year

If len(sParam) 0 then
sSQL = "Year=2007" & " and " & sParam
(value= "Year=2007 and salesrep = 'RosieS'")

Now I'm prompted to enter a parameter for salesrep - where does that come
from?

My report has a query that returns all records - no parameters or other
criteria.

Query... only criteria is that the job ID's exist...

SELECT [Main Job Log].[Job #], [T_Year].[Year] AS [Year], [Main Job
Log].[Owner], [Main Job Log].[Name], [Main Job Log].[City], [Main Job
Log].[Manufacturer], [Main Job Log].[RoofSystem], [Main Job Log].[SqFt],
[Main Job Log].[SalesRep], [T_Year].[Year]
FROM [Main Job Log] INNER JOIN T_Year ON ([Main Job Log].[Job
ORDER BY [Main Job Log].[Job #];
....end query

...frmReport snipit....
sReportName = sRptName
sWhereFieldName = Me.lbl_WhereFieldName.Caption
sWhereFieldValue = Me.cbo_RptOptionList.Value

If Me.cbo_Year.Visible = True Then
sYear = "Year=" & Me.cbo_Year.Value
Else: sYear = ""
End If

If Me.cbo_RptOptionList.Value = "_All" Then
sWhere = ""
Else:
sWhere = sWhereFieldName & " = '" & sWhereFieldValue & "'"
End If

Select Case Len(Left(sYear, 1)) + Len(Left(sWhere, 2))
Case 3
sQry = sYear & " and " & sWhere
Case 2
sQry = sWhere
Case 1
sQry = sYear
Case 0
sQry = sWhere
End Select

DoCmd.OpenReport sReportName, acViewPreview, , sQry
....end snipit....

On Friday, April 04, 2008 12:00 AM
Allen Browne wrote:

Try running the report's query on its own.Does it ask for the parameter?
Try running the report's query on its own.
Does it ask for the parameter?
YES: Then problem is in the query.
a) Is the field really called SalesRep?
Or is it different, e.g. [Sales Rep]
b) Anything in the query's Filter or Order By properties?
(Properties box in query design.)

NO: Then problem is in the report.
a) Open the rpeort in design view, and clear its Filter and OrderBy
properties.
b) Look for controls that refer to the field wrongly (e.g. with the space.)
c) Look in the Sorting And Grouping box, to see if the field is wrong there.
d) If other fields on the report refer to SalesRep, but there is no text box
named SalesRep, try adding a text box with that name and bound to that
field. (Sometimes the Access report optimizer tries to be too clever.)

If you are still stuck, after opening the report, open the Immediate Window
(Ctrl+G), and ask it what the report's filter is, e.g.:
? Reports![Report1].Filter

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JeffP-" wrote in message
...

On Friday, April 04, 2008 2:01 AM
Jeff wrote:

Thanks for your reply, see my inline...
Thanks for your reply, see my inline... I may post a followup as to my
solution or continued horrors....

"Allen Browne" wrote:

No, it quickly opens the entire table; note that if I choose my date as year
and the _All, then the where is only ..."where year=2007" and I'm not promted
the report runs and returns all the data for 2007, it's only when I add to
the where as, ..."where year=2007 and SalesRep = 'RosieS'" that I'm now
promted, the same if I choose manufacturer or any other field.

No, as you can see from the query it's SalesRep, as noted manufacture has
the same result, prompt to enter a parameter dialog, and of course the report
fails to run.

None, there is nothing, further this report was working for manufacturer, it
was during the addition of adding SalesRep in the where SQL string that this
began.

Filter is empty and order value = No

The controls get the fieldname and value from lookups, the form's cbo
fieldname and lookup list are dynamic similar to the Access Switchboard.

None

Yes, there is something here that is trying to be too clever. The form when
launched from different swichboard controls refrences a different item in my
reports table, which indicate the target form, report name, query for the
combo and the lable for the combo.

That's a good step, I was trying to think of how I could trap that moment
because no matter what I enter for a parameter value the report fails, unless
I choose nothing as my sql where string - see my snipit.

On Saturday, April 05, 2008 4:03 PM
NetworkTrad wrote:

I think you may have misinterpreted AB's advice.
I think you may have misinterpreted AB's advice.

You are getting a parameter prompt upon the opening of a report. There can
only be 2 reasons; the query (that is the source of the report) or the
report

If you open the query standalone and it does not have a parameter prompt....
then it is not the query.

This means that in the report there are controls (fields) in the report -
that can not find any data field in the underlying query when the report
attempts to open... This is not uncommon if someone redesigns to underlying
query but forgets to delete old controls from the report.

Also this same logic is valid for subreports and their queries as well....

That parameter prompt is telling you which field is being sought ...and that
is a helpful hint....
--
NTC


"JeffP-" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Customized Find Control for FlowDocuments
http://www.eggheadcafe.com/tutorials...ind-contr.aspx
  #6  
Old May 21st, 2010, 01:14 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default parameter prompt

mal moores wrote:

This thread saved my sanity!

I had a union query based on a number of other queries, all ran fine and returned the correct data.
My form and subform showed all the correct data and functioned perfectly.

When I ran the report based on the same query and the data I could see on screen I got the dreaded parameter prompt!!!

Although the above pointed to the report being the problem I even ended up rewriting the queries etc.
After two days and a lot of ~#~~~#**~~~ etc. I found this thread :0) thank god!!!
Two minutes later all working fine no parameter prompt.

SOLUTION:
Very last line on the screen in grouping/sorting was the word "Expression"? I clicked and expanded and there it was the **##***#~~~*!!!!! sort on "ATRecyID" I couldn't even remember adding this line, my data was sorted in the queries it wasn't required - deleated and away we go, perfect.

To be honest it should have been "NRRecyID" but it wasn't needed!

Hope this may help your sanity!

AB to the rescue again :0)



That may work in your report, but it may not work if you
change your report or use the same approach in another
report. The reason I say that is because sorting a report's
record source query is only effective if the report has
nothing in its Sorting and Grouping. As soon as you put
anything in Sorting and Grouping, it takes precedence over
sorting in the query. In general, sorting a report's record
source query is a waste of resources and Sorting and
Grouping should always be used.

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.