Recordset from query

  • Thread starter Thread starter redrover
  • Start date Start date
R

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?
 
redrover said:
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?
Click to expand...

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.
 
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?
 
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()
 
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?
 
Back
Top