too few parameters on openrecordset

[Not Me]

Hi,

When I do the following in vba:

Set rst = db.OpenRecordset(sql)

I get the too few parameters (expected 3) error. I've seen quite a lot of
this on google but all seem to be caused by incorrect sql, but mine works
fine when I print it out into a query. From what I can tell the parameters
are optional so I don't really understand the problem.

I have dim-ed the recordset as dao.recordset.

Cheers,
Chris

[Allen Browne]

The error message means that JET is unable to resolve some of the names in
the query.

If the query contains things to Forms!SomeForm!SomeControl, it will work in
the query window, but not in code. The solution is to concatenate the value
of the controls into the sql string. That is, instead of:
sql = "SELECT ... WHERE SomeField = Forms!SomeForm!SomeControl;"
use:
sql = "SELECT ... WHERE SomeField = " & Forms!SomeForm!SomeControl & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Not Me" <su**@sumwhere.fake> wrote in message
news:bp**********@ucsnew1.ncl.ac.uk...

Hi,

When I do the following in vba:

Set rst = db.OpenRecordset(sql)

I get the too few parameters (expected 3) error. I've seen quite a lot of
this on google but all seem to be caused by incorrect sql, but mine works
fine when I print it out into a query. From what I can tell the parameters are optional so I don't really understand the problem.

I have dim-ed the recordset as dao.recordset.

[Not Me]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

"Not Me" <su**@sumwhere.fake> wrote in message
news:bp**********@ucsnew1.ncl.ac.uk...

When I do the following in vba:
Set rst = db.OpenRecordset(sql)
I get the too few parameters (expected 3) error. I've seen quite a lot of this on google but all seem to be caused by incorrect sql, but mine works fine when I print it out into a query. From what I can tell the parameters

are optional so I don't really understand the problem.

The error message means that JET is unable to resolve some of the names in
the query.

If the query contains things to Forms!SomeForm!SomeControl, it will work in the query window, but not in code. The solution is to concatenate the value of the controls into the sql string. That is, instead of:
sql = "SELECT ... WHERE SomeField = Forms!SomeForm!SomeControl;"
use:
sql = "SELECT ... WHERE SomeField = " & Forms!SomeForm!SomeControl & ";"

Nice one, stupid mistake to make I guess.

Cheers,
Chris