OpenRecordset method

[Sunnyrain]

I am developing a program in Access 2000. I couldn't make OpenRecordset
method work right.

It's working when I opened a simple SQL query below in OpenRecordset.

.....
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Select * from ATest")
.....

But when I tried to use the value of Text Box object in a SQL query's
criteria, it warned me "Run-time error '3061', Too few parameters.
Expected 4". The SQL query is as below:

Select * from ATest
Where Date >[Forms]![myForm].txtDate

Thanks for your time and help.

[Sunnyrain]

Came cross an article below that sorted out the problem.
URL:
http://msdn.microsoft.com/library/de...ml/sa00e19.asp

The answer is that you're invoking the Jet engine in a different
context here, and that makes all the difference. When you get data from
a parameter query that uses a form to supply the parameter via the
Access user interface, as in the earlier example, Access can evalute
the expression involved and supply a value to Jet. When you get data
from a parameter query that uses a form to supply the parameter via
VBA, instead of through a form, the bits of Access that manage user
interface matters aren't involved. Consequently, Jet is passed the
string "[Forms]![frmSelectCountry]![cboCountry]" instead of the value
in cboCountry. Because Jet doesn't know how to evaluate the
expression, it can't open the recordset.

Thanks

[Douglas J. Steele]

Database is a DAO object. By default, Access 2000 uses ADO. You've obviously
set a reference to Microsoft DAO 3.6 Object Library, or else the declaration
"Dim dbs As Database" would have raised an error, but when you did so, you
must not have unchecked the reference to Microsoft ActiveX Data Objects 2.1
Library (ADO)

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rst as DAO.Recordset (to guarantee an ADO recordset,
you'd use Dim rst As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Sunnyrain" <so******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am developing a program in Access 2000. I couldn't make OpenRecordset
method work right.

It's working when I opened a simple SQL query below in OpenRecordset.

....
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Select * from ATest")
....

But when I tried to use the value of Text Box object in a SQL query's
criteria, it warned me "Run-time error '3061', Too few parameters.
Expected 4". The SQL query is as below:

Select * from ATest
Where Date >[Forms]![myForm].txtDate

Thanks for your time and help.

[fredg]

On 11 Dec 2005 14:59:37 -0800, Sunnyrain wrote:

I am developing a program in Access 2000. I couldn't make OpenRecordset
method work right.

It's working when I opened a simple SQL query below in OpenRecordset.

....
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Select * from ATest")
....

But when I tried to use the value of Text Box object in a SQL query's
criteria, it warned me "Run-time error '3061', Too few parameters.
Expected 4". The SQL query is as below:

Select * from ATest
Where Date >[Forms]![myForm].txtDate

Thanks for your time and help.

You need to wrap the text control with the date delimiter #, and also
concatenate it into the where clause string, so that Access looks for
the Data within the control and not the literal text
"[Forms]![myForm].txtDate ".

(Select * from ATest Where DateField > #" & [Forms]![myForm].txtDate &
"#")

The form MyForm must be open.
If this code is placed in the form MyForm itself, you can use:
Where Date > #" & Me!txtDate & "#")

Note: Do you really have a field named date?
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[John Welch]

The openrecordset method expects a string as the SQL, so it interprets
[Forms]![myform ... as just part of that string and doesn't get the value of
the textbox. To get the value of the textbox in there, you have to do this:
sest rst= dbs.OpenRecordset("Select * from ATest Where Date > " &
[Forms]![myForm].txtDate)
hope that helps
-John

"Sunnyrain" <so******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am developing a program in Access 2000. I couldn't make OpenRecordset
method work right.

It's working when I opened a simple SQL query below in OpenRecordset.

....
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Select * from ATest")
....

But when I tried to use the value of Text Box object in a SQL query's
criteria, it warned me "Run-time error '3061', Too few parameters.
Expected 4". The SQL query is as below:

Select * from ATest
Where Date >[Forms]![myForm].txtDate

Thanks for your time and help.

[David W. Fenton]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:fp********************@rogers.com:

Database is a DAO object. By default, Access 2000 uses ADO. You've
obviously set a reference to Microsoft DAO 3.6 Object Library, or
else the declaration "Dim dbs As Database" would have raised an
error, but when you did so, you must not have unchecked the
reference to Microsoft ActiveX Data Objects 2.1 Library (ADO)

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same
names exist in the 2 models. For example, to ensure that you get a
DAO recordset, you'll need to use Dim rst as DAO.Recordset (to
guarantee an ADO recordset, you'd use Dim rst As ADODB.Recordset)

The list of objects with the same names in the 2 models is
Connection, Error, Errors, Field, Fields, Parameter, Parameters,
Property, Properties and Recordset

It is best practice to disambiguate all such references:

Dim rs As DAO.Recordset

in all cases. That way, you never have to worry about the issue at
all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc