HELP-can't open query recordset

A

Alan Baker

9/1/2005



I am having difficulty opening a query recordset for analysis.

The following code opens the query with no problems when the query has
either no criteria or criteria specified as constants. When I attempt to put
a variable in the query criteria, it stops at the line



.Open qryName, Options:=adCmdTableDirect





with the Run Time Error that says "Too few parameters. Expected x" where x
is the number of variables specified in the criteria



Can you tell me what is wrong and how to fix it?



I will post this to a couple of newsgroups because I'm not sure which one is
appropriate.

If possible, please respond to me directly at [email protected]. as I am
a newbie to these newsgroups.



Many many thanks.



Al Baker







Sub QueryTableLookup()



Const qryName As String = "qryVerifyDeleteRecords"



Dim rstqryEquipOne As ADODB.Recordset



Set rstqryEquipOne = New ADODB.Recordset

Set rstqryEquipOne.ActiveConnection = CurrentProject.Connection





With rstqryEquipOne

.CursorType = adOpenKeyset

.Open qryName, Options:=adCmdTableDirect

End With



DoCmd.OpenQuery qryName, acNormal, acEdit

rstqryEquipOne.MoveFirst



LastEquipId = rstqryEquipOne!ID





Stop

End
 
J

John Vinson

9/1/2005



I am having difficulty opening a query recordset for analysis.

The following code opens the query with no problems when the query has
either no criteria or criteria specified as constants. When I attempt to put
a variable in the query criteria, it stops at the line



.Open qryName, Options:=adCmdTableDirect
Click to expand...

Try:

Dim prm As Parameter
Dim rs As DAO.Recordset
Dim qd As DAO.Querydef
Dim db As DAO.Database

Set db = CurrentDb
Set qd = db.OpenQuerydef("qryName", acCmdTableDirect) ' may need tweak
For Each prm in qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qd.OpenRecordset
I will post this to a couple of newsgroups because I'm not sure which one is
appropriate.
Click to expand...

If you do so - please crosspost by putting the two or three (NO MORE!)
newsgroups on the newsgroup line, rather than multiposting the same
message twice. This helps the volunteers identify questions which have
already been answered.
If possible, please respond to me directly at [email protected]. as I am
a newbie to these newsgroups.
Click to expand...

Done... but please be aware that this is considered an impolite
request. Ask here, get the answers here; private EMail support is for
paying customers. Like most here, I'm a volunteer donating my time on
the newsgroups, and have to try to help the users of the group in
general, not individual parties.

John W. Vinson[MVP]
 
Top