By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Parameterised query question

P: n/a
Hi

I have a parameterised query for a report like this;

PARAMETERS Event_ID Short;
SELECT DISTINCTROW Events.[Event ID], ...
FROM Events
WHERE (((Events.[Event ID])=[Event_ID]));

Now I am trying to run the report while passing the parameter value like
this;

docmd.OpenReport "My Report",acViewPreview,,"[Event_ID] = 6736"

it does not work and the parameter entry dialog still appears to read a
value manually. How can I make it work via code?

Thanks

Regards
Dec 29 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Fri, 29 Dec 2006 05:20:11 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:
>Hi

I have a parameterised query for a report like this;

PARAMETERS Event_ID Short;
SELECT DISTINCTROW Events.[Event ID], ...
FROM Events
WHERE (((Events.[Event ID])=[Event_ID]));

Now I am trying to run the report while passing the parameter value like
this;

docmd.OpenReport "My Report",acViewPreview,,"[Event_ID] = 6736"

it does not work and the parameter entry dialog still appears to read a
value manually. How can I make it work via code?
You're missing the point of how parameters work, or how the OpenReport
method works. If you're using the WhereCondition argument in
OpenReport to pass a literal value, you don't NEED a parameter. You
could just base the Report directly on Events and use

docmd.OpenReport "My Report",acViewPreview,,"[Event ID] = 6736"
John W. Vinson[MVP]
Dec 29 '06 #2

P: n/a
Hi, John.

To add to what others have advised, if you ever need a parameter (and most of
the time you won't), you can create one in the WHERE clause, instead of
declaring the parameter with the PARAMETERS key word and selecting the data
type. Try:

SELECT DISTINCTROW Events.[Event ID], Col1, Col2
FROM Events
WHERE (((Events.[Event ID])=[Enter Event ID:]));

This will prompt the user with:

Enter Event ID:

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
Hi

I have a parameterised query for a report like this;

PARAMETERS Event_ID Short;
SELECT DISTINCTROW Events.[Event ID], ...
FROM Events
WHERE (((Events.[Event ID])=[Event_ID]));

Now I am trying to run the report while passing the parameter value like this;

docmd.OpenReport "My Report",acViewPreview,,"[Event_ID] = 6736"

it does not work and the parameter entry dialog still appears to read a value
manually. How can I make it work via code?

Thanks

Regards


Dec 29 '06 #3

P: n/a
On Fri, 29 Dec 2006 00:57:54 -0800, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:
>To add to what others have advised, if you ever need a parameter (and most of
the time you won't), you can create one in the WHERE clause, instead of
declaring the parameter with the PARAMETERS key word and selecting the data
type.
That's *usually* true - but some queries are quirky. For instance a
Crosstab query requires the use of the PARAMETER declaration, and
gives a confusing error message if it's omitted.

Declaring a parameter is an extra step, but IME it never hurts to do
so.

John W. Vinson[MVP]
Dec 29 '06 #4

P: n/a
Hi, John.
For instance a
Crosstab query requires the use of the PARAMETER declaration, and
gives a confusing error message if it's omitted.
Excellent point.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.comwrote in message
news:k0********************************@4ax.com...
On Fri, 29 Dec 2006 00:57:54 -0800, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:
>>To add to what others have advised, if you ever need a parameter (and most of
the time you won't), you can create one in the WHERE clause, instead of
declaring the parameter with the PARAMETERS key word and selecting the data
type.

That's *usually* true - but some queries are quirky. For instance a
Crosstab query requires the use of the PARAMETER declaration, and
gives a confusing error message if it's omitted.

Declaring a parameter is an extra step, but IME it never hurts to do
so.

John W. Vinson[MVP]

Dec 29 '06 #5

P: n/a
Another option is You just need to define the parameters before you use
the report:
Public Sub DemoParameters()

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter

' Before running this procedure, open
' frmInfo and enter a value, like "Berlin",
' into the City text box, then tab off of the text box.
' You must move the focus out of the City text box in
' order for this to work.
Stop
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qryCustCity"
' If you use adCmdStoredProc,
' this won't work
cmd.CommandType = adCmdTable

' This next statement is actually optional
' If you leave it out, ADO does it anyway.
cmd.Parameters.Refresh
' Loop through the parameters
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm

' And populate the recordset
Set rst = cmd.Execute
Do Until rst.EOF
Debug.Print rst.Fields(0).Value
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cmd = Nothing
End Sub
then run report
docmd.runreport
this is an excerpt from access 2002 desktop developers handbook i use I
cut out the part i need where it steps through the parameters and put
the query name in the commandtext var. it will step through the
parameters in the query and then when you run the report it will be
full.
Lewie

'69 Camaro wrote:
Hi, John.
For instance a
Crosstab query requires the use of the PARAMETER declaration, and
gives a confusing error message if it's omitted.

Excellent point.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.comwrote in message
news:k0********************************@4ax.com...
On Fri, 29 Dec 2006 00:57:54 -0800, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:
>To add to what others have advised, if you ever need a parameter (and most of
the time you won't), you can create one in the WHERE clause, instead of
declaring the parameter with the PARAMETERS key word and selecting the data
type.
That's *usually* true - but some queries are quirky. For instance a
Crosstab query requires the use of the PARAMETER declaration, and
gives a confusing error message if it's omitted.

Declaring a parameter is an extra step, but IME it never hurts to do
so.

John W. Vinson[MVP]
Dec 29 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.