query works differently called from report or ADO

[Larry Marshall]

I am using a query as a source of both a report and a stored procedure
in ADO code.

In the where clause of the query it references a date field on the
form which is calling it (either the report or in vba ADO code).
Running the report works fine. However, in the following code,

Set cmd1 = New ADODB.Command
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset

With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "qryMailingsPending5"
.CommandType = adCmdStoredProc
End With
Set rst1 = cmd1.Execute

I get an error on the last statement, "Too few parameters. Expected
1."

The field I reference in the query is:
FORMS![frmSelectProcessing]![txtAsOfDate]

I would appreciate any help on this.
TIA - Larry

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is the query "qryMailingsPending5" really a stored procedure? SPs
can't use Form references as parameters unless you set up the
ADO.Command object's Parameters collection (in VBA).

JET queries use a Form reference for parameters.

See the ADO Help articles on Command object Parameters collection.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCQYHYechKqOuFEgEQK2HwCfRUiqlESrXVwTAlsrLQlA8RVR+sgAoMTD
D3dpJ5qqIJihJrltimeRwJPZ
=LfzE
-----END PGP SIGNATURE-----

 

[Larry Marshall]

You're right, it's a query, not an SP. But the query works properly
when called by the report, why not in the ADO code? I'm still lost.

Larry

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to set the ADO.Command Parameters collection - you are not
doing that. You also have to change the adCmdStoredProcedure to
adCmdTable (queries are considered tables). Read the ADO Help article
on the Command object Parameters collection: you have to create a
parameter & then append it to the instance of command object.

If it were me I'd use DAO - much easier than ADO.

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("query name")
qd.execute dbFailOnError

The form that holds the data must be open for the qd.Execute to
succeed.

For a recordset

dim rs as dao.recordset
....
qd.parameters(0) = eval(qd.parameters(0))
set rs = qd.openrecordset()

For some reason I don't understand, when setting the query parameter
value for a Forms!FormName!ControlName we have to put the
qd.parameters(?) inside the eval() function.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCQ304echKqOuFEgEQJr0wCg5xQXucXMp0bmdXxvpBU671B/WLsAoLNc
t+Ku3VDXxGuo/U9OCeDDkpd1
=xeL1
-----END PGP SIGNATURE-----