too few parameters error

[Karen]

I am reposting this because I'm afraid it's too old to be found and
answered.


I have the following code that I'm trying to use to 'view' one record of a
SQL table that I've linked to with Access 2002. This code is behind a
button on a form. On the form I enter a value in a control named custno.
When I click the button I get an error that says "Too few parameters.
Expected 1." In the SQL table, filler_0001 is a text field.
If I change the MyCriteria line to MyCriteria = Me!Custno & "'"
and I change the MySql line to
MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE dbo_oeprcfil_sql.[filler_0001]
= '"

I get an error 'Type Mismatch'
The value I'm entering in the control on the form is 0000000021923920-M
----------------
Dim rst As Recordset
Dim MyWksp As Workspace
Dim MyDB As Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] ="
MyCriteria = Me!Custno

Set rst = MyDB.OpenRecordset(MySQL & MyCriteria, dbOpenForwardOnly)

If Not rst.EOF Then
MsgBox ("CustomerNumber/Code = ")
------------------


Help!!!

Thank you (yet again!)
Karen

 

[Douglas J Steele]

filler_0001 must be a text field given the same data, so you need quotes
around whatever value you're passing to it:

Set rst = MyDB.OpenRecordset(MySQL & Chr$(39) & MyCriteria & Chr$(39),
dbOpenForwardOnly)

or

Set rst = MyDB.OpenRecordset(MySQL & "'" & MyCriteria & "'",
dbOpenForwardOnly)

where those are " ' " being added at either side.

 

[Douglas J Steele]

In addition to my suggestion below, what version of Access are you using?

You're trying to use DAO code below. By default, Access 2000 and 2002 only
have references to ADO. Since your code isn't giving you compile errors, if
you're using one of those two versions, you've obviously added a reference
to DAO. However, did you remove the reference to ADO at the same time?

Recordset is an object in both the DAO and ADO models. When you use

Dim rst As Recordset

Access is going to use whichever model it finds first with a Recordset
object in it. Since you added DAO after ADO, that means it's treating rst as
an ADO recordset.

Try


Dim rst As DAO.Recordset

(to ensure that you were getting an ADO recordset, you'd use Dim rst As
ADODB.Recordset)

Even when you don't have both references set, it's always a good idea to
disambiguate like this for clarity.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

filler_0001 must be a text field given the same data, so you need quotes
around whatever value you're passing to it:

Set rst = MyDB.OpenRecordset(MySQL & Chr$(39) & MyCriteria & Chr$(39),
dbOpenForwardOnly)

or

Set rst = MyDB.OpenRecordset(MySQL & "'" & MyCriteria & "'",
dbOpenForwardOnly)

where those are " ' " being added at either side.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am reposting this because I'm afraid it's too old to be found and
answered.


I have the following code that I'm trying to use to 'view' one record of a
SQL table that I've linked to with Access 2002. This code is behind a
button on a form. On the form I enter a value in a control named custno.
When I click the button I get an error that says "Too few parameters.
Expected 1." In the SQL table, filler_0001 is a text field.
If I change the MyCriteria line to MyCriteria = Me!Custno & "'"
and I change the MySql line to
MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE dbo_oeprcfil_sql.[filler_0001]
= '"

I get an error 'Type Mismatch'
The value I'm entering in the control on the form is 0000000021923920-M
----------------
Dim rst As Recordset
Dim MyWksp As Workspace
Dim MyDB As Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] ="
MyCriteria = Me!Custno

Set rst = MyDB.OpenRecordset(MySQL & MyCriteria, dbOpenForwardOnly)

If Not rst.EOF Then
MsgBox ("CustomerNumber/Code = ")
------------------


Help!!!

Thank you (yet again!)
Karen

 

[Karen]

The code change worked! Now it reads as follows
---------------
Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] = "

MyCriteria = Me!Custno & Me!code

Set rst = MyDB.OpenRecordset(MySQL & "'" & MyCriteria & "'",
dbOpenForwardOnly)
------------------
I'm afraid I am clueless when it comes to anything about ADO versus DAO.
Where can I go to read more about this and how it affects my programing.
I'm entirely self taught (with the help of these newsgroups thanks).

I'm using Access 2002 right now. I'm supposed to be getting a new computer
in the first quarter of 2006 and it will be an XP machine. I don't know
what version of Access will be installed on it.


Thank you for your help. I was totally lost. I'm still trying to get the
syntax correct on these types of statements.
Karen

In addition to my suggestion below, what version of Access are you using?

You're trying to use DAO code below. By default, Access 2000 and 2002 only
have references to ADO. Since your code isn't giving you compile errors, if
you're using one of those two versions, you've obviously added a reference
to DAO. However, did you remove the reference to ADO at the same time?

Recordset is an object in both the DAO and ADO models. When you use

Dim rst As Recordset

Access is going to use whichever model it finds first with a Recordset
object in it. Since you added DAO after ADO, that means it's treating rst as
an ADO recordset.

Try


Dim rst As DAO.Recordset

(to ensure that you were getting an ADO recordset, you'd use Dim rst As
ADODB.Recordset)

Even when you don't have both references set, it's always a good idea to
disambiguate like this for clarity.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

filler_0001 must be a text field given the same data, so you need quotes
around whatever value you're passing to it:

Set rst = MyDB.OpenRecordset(MySQL & Chr$(39) & MyCriteria & Chr$(39),
dbOpenForwardOnly)

or

Set rst = MyDB.OpenRecordset(MySQL & "'" & MyCriteria & "'",
dbOpenForwardOnly)

where those are " ' " being added at either side.

of

a

SQL table that I've linked to with Access 2002. This code is behind a
button on a form. On the form I enter a value in a control named custno.
When I click the button I get an error that says "Too few parameters.
Expected 1." In the SQL table, filler_0001 is a text field.
If I change the MyCriteria line to MyCriteria = Me!Custno & "'"
and I change the MySql line to
MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE dbo_oeprcfil_sql.[filler_0001]
= '"

I get an error 'Type Mismatch'
The value I'm entering in the control on the form is 0000000021923920-M
----------------
Dim rst As Recordset
Dim MyWksp As Workspace
Dim MyDB As Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] ="
MyCriteria = Me!Custno

Set rst = MyDB.OpenRecordset(MySQL & MyCriteria, dbOpenForwardOnly)

If Not rst.EOF Then
MsgBox ("CustomerNumber/Code = ")
------------------


Help!!!

Thank you (yet again!)
Karen

 

[Douglas J Steele]

If you're strictly programming against Jet databases (i.e.: only MDB files),
there's no real reason to worry about ADO. DAO was developed specifically
for Jet databases, whereas ADO is a more generic approach (meaning you have
to go through more levels of abstraction). On the other hand, if there's a
change that you might upsize your back end to a different DBMS in the
future, ADO might make more sense.

Try going to http://search.microsoft.com/search/search.aspx?st=a&View=en-us
and putting ADO DAO in the "All of these words:" box. You'll get lots of
reading material! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The code change worked! Now it reads as follows
---------------
Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] = "

MyCriteria = Me!Custno & Me!code

Set rst = MyDB.OpenRecordset(MySQL & "'" & MyCriteria & "'",
dbOpenForwardOnly)
------------------
I'm afraid I am clueless when it comes to anything about ADO versus DAO.
Where can I go to read more about this and how it affects my programing.
I'm entirely self taught (with the help of these newsgroups thanks).

I'm using Access 2002 right now. I'm supposed to be getting a new computer
in the first quarter of 2006 and it will be an XP machine. I don't know
what version of Access will be installed on it.


Thank you for your help. I was totally lost. I'm still trying to get the
syntax correct on these types of statements.
Karen

In addition to my suggestion below, what version of Access are you using?

You're trying to use DAO code below. By default, Access 2000 and 2002 only
have references to ADO. Since your code isn't giving you compile errors, if
you're using one of those two versions, you've obviously added a reference
to DAO. However, did you remove the reference to ADO at the same time?

Recordset is an object in both the DAO and ADO models. When you use

Dim rst As Recordset

Access is going to use whichever model it finds first with a Recordset
object in it. Since you added DAO after ADO, that means it's treating

rst

as
an ADO recordset.

Try


Dim rst As DAO.Recordset

(to ensure that you were getting an ADO recordset, you'd use Dim rst As
ADODB.Recordset)

Even when you don't have both references set, it's always a good idea to
disambiguate like this for clarity.

record

of

a
SQL table that I've linked to with Access 2002. This code is behind a
button on a form. On the form I enter a value in a control named custno.
When I click the button I get an error that says "Too few parameters.
Expected 1." In the SQL table, filler_0001 is a text field.
If I change the MyCriteria line to MyCriteria = Me!Custno & "'"
and I change the MySql line to
MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001]
= '"

I get an error 'Type Mismatch'
The value I'm entering in the control on the form is 0000000021923920-M
----------------
Dim rst As Recordset
Dim MyWksp As Workspace
Dim MyDB As Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)

MySQL = "SELECT * FROM dbo_oeprcfil_sql WHERE
dbo_oeprcfil_sql.[filler_0001] ="
MyCriteria = Me!Custno

Set rst = MyDB.OpenRecordset(MySQL & MyCriteria, dbOpenForwardOnly)

If Not rst.EOF Then
MsgBox ("CustomerNumber/Code = ")
------------------


Help!!!

Thank you (yet again!)
Karen