Queries and queries

[Rudy W.]

Dim dbsTest As DAO.Database
Dim strTest As String
Dim rstTest As DAO.Recordset
strTest = "SELECT Val([Forms]![frmTest]![testField]) As
test FROM tblTestFields WHERE fieldname = 'testField';"
Set rstTest = dbsTest.OpenRecordset(strTest,dbOpenDynaset)

Gives an error:

3061 Too few parameters. Expected 1

The form "frmTest" is open and the field "testField" is
accessable on the moment of the execution.
Also the tabel "tblTestFields" is available and there is
a record with value 'testField' for the field "fieldname".

When I run the same query:
SELECT Val([Forms]![frmTest]![testField]) As test FROM
tblTestFields WHERE fieldname = 'testField';
direct from the query chapter, I get a valid result
without errors.

Thanks

R.W.

 

[Guest]

Have you tried setting the dbsTest variable, e.g.

set dbsTest = currentdb()

Ashley J Bragg
Consultant

 

[John Nurick]

Hi Rudy,

You need to put the actual value from the form into the SQL string as
you build it. This works in my test database:

Dim dbsTest As DAO.Database
Dim strTest As String
Dim rstTest As DAO.Recordset

Set dbsTest = CurrentDb()

strTest = "SELECT " _
& CStr(Nz(Forms!frmTest!Text5, "NULL")) _
& " As TestVal FROM T97C WHERE Field2 = 'Suzanna';"
Set rstTest = dbsTest.OpenRecordset(strTest, dbOpenDynaset)

Dim dbsTest As DAO.Database
Dim strTest As String
Dim rstTest As DAO.Recordset
strTest = "SELECT Val([Forms]![frmTest]![testField]) As
test FROM tblTestFields WHERE fieldname = 'testField';"
Set rstTest = dbsTest.OpenRecordset(strTest,dbOpenDynaset)

Gives an error:

3061 Too few parameters. Expected 1

The form "frmTest" is open and the field "testField" is
accessable on the moment of the execution.
Also the tabel "tblTestFields" is available and there is
a record with value 'testField' for the field "fieldname".

When I run the same query:
SELECT Val([Forms]![frmTest]![testField]) As test FROM
tblTestFields WHERE fieldname = 'testField';
direct from the query chapter, I get a valid result
without errors.

Thanks

R.W.