Recordset from query

[redrover]

I'm trying to open a recordset based on a query. I'm gettint a message
telling me I have too few parameters. My code is:

Dim rst As ADODB.Recordset
Set rst = CurrentDb.OpenRecordset("qryCompTrScore")

qryCompTrScore is the query I'm wanting to open so I can loop through the
records. What is wrong with code I'm using?

 

[Banana]

I'm trying to open a recordset based on a query. I'm gettint a message
telling me I have too few parameters. My code is:

Dim rst As ADODB.Recordset
Set rst = CurrentDb.OpenRecordset("qryCompTrScore")

qryCompTrScore is the query I'm wanting to open so I can loop through the
records. What is wrong with code I'm using?

Pretty sure you can't open an ADO recordset like that. You need to pass
in the sQL statement...

rst.Open CurrentDb.QueryDefs("qryCompTrScore").SQL

That's assuming there's no Jet stuff that may not work in ADODB (e.g.
control reference most likely won't work) and that's not really
effective way of doing thing snyway. I'd just use SQL directly in the
Open method.

 

[redrover]

Thank you. I tried that coding and I received an error message that the
'object variable or with block variable is not set'. I then add the code:

Set rst = New ADODB.Recordset

Then I received the message 'Connection cannot be used - closed or not valid'.

Any other suggestions?

 

[Arvin Meyer [MVP]]

I don't use ADO except occasionally for SQL-Server. You have to supply a
value to the parameter manually like:

Dim db As DAO.Database
Dim qdf As DAO.Querydef
Dim rst As DAO.Recordset

Set db = Currentdb
Set qdf = db.Querydefs!qryYourParameterQuery

qdf.Parameters!prmParameterName = Forms!MyformName!MycontrolName

' Or you can use the parameter index
' qdf.Parameters(0) = Forms!MyformName!MycontrolName

Set rst = qdf.OpenRecordset()

 

[redrover]

Thanks for the help. I'm still having a little trouble. Here's my code:

Dim MyDb As DAO.Database
Dim qdf As DAO.Querydef
Dim rst As DAO.Recordset
Set MyDb = Currentdb
Set qdf = MyDb.QueryDefs!qryCompTrScore
Set rst = qdf.OpenRecordset()

I'm getting an error message on the last set statement -- 'Too few
parameters. Expected 4.'

Can you point me in the right direction? I just want to open the dataset to
I can read the records. Is there another way to open the recordset based on
the query?

 

[Arvin Meyer [MVP]]

You have not names your parameter. Look again at my code below. I gave you 2
methods for doing that. You MUST name your parameter and tell your code what
the value is BEFORE you open the recordset.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com