Parameterised query question

[John]

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

 

[John Vinson]

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]

 

[Allen Browne]

Omit the PARAMETERS and WHERE Clause from the query.

Just using the WhereCondition of OpenReport will filter the report.

 

['69 Camaro]

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/expert_contributors2.html for contact info.

 

[John Vinson]

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]

 

['69 Camaro]

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/expert_contributors2.html for contact info.

 

[lewie]

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