Never-Ending Parameter request!

  • Thread starter Thread starter MariK
  • Start date Start date
M

MariK

I have a problem with a report that, when run, wants me
to put in the "town" (Enter Parameter) once for every
record in the sub-report. This is not bad when I only
have 5 records on the sub-report, but it gets frustrating
when I have 500! This worked fine until I added a "like"
statement to the town field so I could print for a
specific town instead of all towns. Why is this
happening?
 
Post the SQL of the query and let's see. If you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button on it. The user enters the desired
parameter into the textbox, then clicks the button. The button opens the
report, whose recordsource query reads the needed parameter from the form's
textbox.
 
MariK said:
I have a problem with a report that, when run, wants me
to put in the "town" (Enter Parameter) once for every
record in the sub-report. This is not bad when I only
have 5 records on the sub-report, but it gets frustrating
when I have 500! This worked fine until I added a "like"
statement to the town field so I could print for a
specific town instead of all towns. Why is this
happening?
Click to expand...

I noticed a similar behavior and it was due to oversight ... or fatigue.

In the report's query, I defined the parameter [Item Number?]. (Query =>
Parameters)

In the Criteria portion of the query, I used the string [Item Number].

Since [Item Number] != [Item Number?] I was getting prompted twice each time
I ran the report until I changed them to be identical.
 
-----Original Message-----
Post the SQL of the query and let's see. If you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button on it. The user enters the desired
parameter into the textbox, then clicks the button. The button opens the
report, whose recordsource query reads the needed parameter from the form's
textbox.

-- Here is the SQL of the query. Thanks for your help!
Click to expand...
SELECT [Team Information].Division, [Team Information].
[Team Name], [Operator Information].OperatorName, [Player
Information].[Last Name], [Player Information].[Team ID]
FROM ([Operator Information] INNER JOIN [Player
Information] ON [Operator Information].Code = [Player
Information].Code) INNER JOIN [Team Information] ON
([Team Information].[Team ID] = [Player Information].
[Team ID]) AND ([Operator Information].Code = [Team
Information].Code)
WHERE ((([Team Information].Division) Like [Enter
Division] & "*"))
ORDER BY [Team Information].Division, [Team Information].
[Team Name], [Player Information].[Last Name];
 
-----Original Message-----
Post the SQL of the query and let's see. If you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button on it. The user enters the desired
parameter into the textbox, then clicks the button. The button opens the
report, whose recordsource query reads the needed parameter from the form's
textbox.

-- Here is the SQL of the query. Thanks for your help!
Click to expand...
SELECT [Team Information].Division, [Team Information].
[Team Name], [Operator Information].OperatorName, [Player
Information].[Last Name], [Player Information].[Team ID]
FROM ([Operator Information] INNER JOIN [Player
Information] ON [Operator Information].Code = [Player
Information].Code) INNER JOIN [Team Information] ON
([Team Information].[Team ID] = [Player Information].
[Team ID]) AND ([Operator Information].Code = [Team
Information].Code)
WHERE ((([Team Information].Division) Like [Enter
Division] & "*"))
ORDER BY [Team Information].Division, [Team Information].
[Team Name], [Player Information].[Last Name];
Click to expand...

I am not sure "*" is a valid wildcard for the LIKE statement. There is "%"
that can be used as a leading and/or trailing wildcard (similar to what I
believe was intended by the "*"). Or does Access do a translation from "*"
to "%"?

LIKE 'MYDIV%' would return anything starting with MYDIV

LIKE '%MYDIV%' would return anything with MYDIV somewhere in the string

LIKE '%MYDIV' would return anything ending in MYDIV
 
It comes up the same with or without the "*". My problem
is that it asks me to fill in the Paramter Box for EVERY
conresponding record in the sub-report attached to it. I
need to get around that.
 
It comes up the same with or without the "*". My problem
is that it asks me to fill in the Paramter Box for EVERY
conresponding record in the sub-report attached to it. I
need to get around that.
Click to expand...

Wait a minute ... I just noticed something in this thread.

Your original post states Access "wants me to put in the "town" (Enter
Parameter) once for every record in the sub-report."

In the response from Ken Snell, he states "if you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button it. The user enters the desired
parameter into the textbox, then clicks the button."

It's already been answered. Access is working the way it is designed. You'll
have to capture "town" from the main report and programmatically pass it
down to the subreport to avoid being prompted repeatedly. In other words, a
bit of VBA work behind the scenes. (Big Duh! on my part. Must be the gray
hairs affecting the parity checking of the mynd.)

BTW - the "*" is the DAO way of doing it and the "%" is the ADO way of doing
it from what I found later on in the day. Just never got around to
reposting.
 
As I suspected, your parameter is in a subquery.

Create a form; name it frmDivision. Put a textbox on the form; name it
txtDivision. Put a command button the form; name it cmdRun.

Put the following code on the OnClick event of the cmdRun button:

Private Sub cmdRun_Click()
DoCmd.OpenReport "ReportName"
End Sub

Change the parameter [Enter Division] in your subquery to this:
[Forms]![frmDivision]![txtDivision]

Then, to run your report, open frmDivision, enter the Division name in the
textbox, and click the command button. The query will read the parameter
from the form and you won't need to type it in again.

--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Post the SQL of the query and let's see. If you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button on it. The user enters the desired
parameter into the textbox, then clicks the button. The button opens the
report, whose recordsource query reads the needed parameter from the form's
textbox.

-- Here is the SQL of the query. Thanks for your help!
Click to expand...
SELECT [Team Information].Division, [Team Information].
[Team Name], [Operator Information].OperatorName, [Player
Information].[Last Name], [Player Information].[Team ID]
FROM ([Operator Information] INNER JOIN [Player
Information] ON [Operator Information].Code = [Player
Information].Code) INNER JOIN [Team Information] ON
([Team Information].[Team ID] = [Player Information].
[Team ID]) AND ([Operator Information].Code = [Team
Information].Code)
WHERE ((([Team Information].Division) Like [Enter
Division] & "*"))
ORDER BY [Team Information].Division, [Team Information].
[Team Name], [Player Information].[Last Name];
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Actually, my reference here is a bit cryptic.....there is not a subquery in
this SQL that you posted, but I'm referring to your initial post where you
said that this is the SQL of the query upon which your subreport is based.

--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
As I suspected, your parameter is in a subquery.

Create a form; name it frmDivision. Put a textbox on the form; name it
txtDivision. Put a command button the form; name it cmdRun.

Put the following code on the OnClick event of the cmdRun button:

Private Sub cmdRun_Click()
DoCmd.OpenReport "ReportName"
End Sub

Change the parameter [Enter Division] in your subquery to this:
[Forms]![frmDivision]![txtDivision]

Then, to run your report, open frmDivision, enter the Division name in the
textbox, and click the command button. The query will read the parameter
from the form and you won't need to type it in again.

--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Post the SQL of the query and let's see. If you're running a subquery that
asks for a parameter, then you'll get this problem. Way around it is to use
a form with a textbox and command button on it. The user enters the desired
parameter into the textbox, then clicks the button. The button opens the
report, whose recordsource query reads the needed parameter from the form's
textbox.

-- Here is the SQL of the query. Thanks for your help!
Click to expand...
SELECT [Team Information].Division, [Team Information].
[Team Name], [Operator Information].OperatorName, [Player
Information].[Last Name], [Player Information].[Team ID]
FROM ([Operator Information] INNER JOIN [Player
Information] ON [Operator Information].Code = [Player
Information].Code) INNER JOIN [Team Information] ON
([Team Information].[Team ID] = [Player Information].
[Team ID]) AND ([Operator Information].Code = [Team
Information].Code)
WHERE ((([Team Information].Division) Like [Enter
Division] & "*"))
ORDER BY [Team Information].Division, [Team Information].
[Team Name], [Player Information].[Last Name];
Ken Snell
<MS ACCESS MVP>

I have a problem with a report that, when run, wants me
to put in the "town" (Enter Parameter) once for every
record in the sub-report. This is not bad when I only
have 5 records on the sub-report, but it gets frustrating
when I have 500! This worked fine until I added a "like"
statement to the town field so I could print for a
specific town instead of all towns. Why is this
happening?


.
Click to expand...
Click to expand...
Click to expand...
 
Worked like a charm! Thanks!!
-----Original Message-----
Actually, my reference here is a bit cryptic.....there is not a subquery in
this SQL that you posted, but I'm referring to your initial post where you
said that this is the SQL of the query upon which your subreport is based.

--
Ken Snell
<MS ACCESS MVP>

As I suspected, your parameter is in a subquery.

Create a form; name it frmDivision. Put a textbox on the form; name it
txtDivision. Put a command button the form; name it cmdRun.

Put the following code on the OnClick event of the cmdRun button:

Private Sub cmdRun_Click()
DoCmd.OpenReport "ReportName"
End Sub

Change the parameter [Enter Division] in your subquery to this:
[Forms]![frmDivision]![txtDivision]

Then, to run your report, open frmDivision, enter the Division name in the
textbox, and click the command button. The query will read the parameter
from the form and you won't need to type it in again.

--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Post the SQL of the query and let's see. If you're
running a subquery that
asks for a parameter, then you'll get this problem. Way
around it is to use
a form with a textbox and command button on it. The user
enters the desired
parameter into the textbox, then clicks the button. The
button opens the
report, whose recordsource query reads the needed
parameter from the form's
textbox.

-- Here is the SQL of the query. Thanks for your help!
SELECT [Team Information].Division, [Team Information].
[Team Name], [Operator Information].OperatorName, [Player
Information].[Last Name], [Player Information].[Team ID]
FROM ([Operator Information] INNER JOIN [Player
Information] ON [Operator Information].Code = [Player
Information].Code) INNER JOIN [Team Information] ON
([Team Information].[Team ID] = [Player Information].
[Team ID]) AND ([Operator Information].Code = [Team
Information].Code)
WHERE ((([Team Information].Division) Like [Enter
Division] & "*"))
ORDER BY [Team Information].Division, [Team Information].
[Team Name], [Player Information].[Last Name];

Ken Snell
<MS ACCESS MVP>

message
I have a problem with a report that, when run, wants me
to put in the "town" (Enter Parameter) once for every
record in the sub-report. This is not bad when I only
have 5 records on the sub-report, but it gets
frustrating
when I have 500! This worked fine until I added
a "like"
statement to the town field so I could print for a
specific town instead of all towns. Why is this
happening?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top