Open a Recordset Based on a Query

[jpgactmod]

I thought that opening a Recordset based on a Query was identical to opening a Recordset based on a Table? Apparently not. The below works for Tables in my Database but not for Queries. Any suggestions as to how I can open a Recordset based on my Query named "QryTest"? Here is what I have that gives me an error stating "No value given for one or more required parameters":

Dim db As DAO.Database
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient

Set db = CurrentDb

rs.Open "Select * from QryTest ", Options:=adCmdText


If I replace "QryTest" in the last line above with the name of a Table, the Table opens with no problems. Any idea as to why I cannot open a Query?

Thank you for any suggstions.

Jim

 

[aradha]

Hello

Change your rs.open statement to
ssql = "Select * from QryTest"
rs.open ssql, cn

See if this works

Anu.

 

[RoyVidar]

Does it contain parameters? Here thread709-819033 there's syntax for retrieving a recordset based on a query with parameters from forms dynamicly for both 2000 version and later versions (latter is further down).

Roy-Vidar

 

[jpgactmod]

aradha and RoyVidar,

Thank you both for your suggestions.

aradha - with respect to your suggestion, I get the following error "Run-time error 3709; The connection cannot be used to perform this operation. It is either closed or invalid in this context." Any suggestions (please see below).

RoyVidar - I am not sure what "a query with parameters" means, but I am trying to open a fairly complex Query (one that contains expressions and is linked via VBA code to a list box). I did try a simple Query and it opened with no problem so that may mean the Query I am trying to open has parameters. The link you suggested does not seem to culminate in a solution - am I missing something?

Thanks again to both of you.

Jim