Using text field on form produces run-time error 3061

[Jim Pockmire]

I am using a text field on a form in a parameter query,which in turn is
opened as a recordset in code. Opening the recordset produces run-time error
3061 (too few parameters).

What is the fix?

 

[Allen Browne]

You can programmatically supply the parameter before you OpenRecordset,
e.g.:
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
set rs = qdf.OpenRecordset()

It is generally simpler (and more flexible) to just create the query
statement on the fly instead of using a saved query:
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE Field1 = " & Forms!Form1!Text0
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

 

[Jim Pockmire]

I guess you are also saying that the error is normal behavior.

You can programmatically supply the parameter before you OpenRecordset,
e.g.:
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
set rs = qdf.OpenRecordset()

It is generally simpler (and more flexible) to just create the query
statement on the fly instead of using a saved query:
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE Field1 = " & Forms!Form1!Text0
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using a text field on a form in a parameter query,which in turn is
opened as a recordset in code. Opening the recordset produces run-time
error 3061 (too few parameters).

What is the fix?

 

[Douglas J. Steele]

Yes, the error is normal behaviour unless you provide the values to the
parameters, such as how Allen showed you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I guess you are also saying that the error is normal behavior.

You can programmatically supply the parameter before you OpenRecordset,
e.g.:
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
set rs = qdf.OpenRecordset()

It is generally simpler (and more flexible) to just create the query
statement on the fly instead of using a saved query:
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE Field1 = " & Forms!Form1!Text0
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using a text field on a form in a parameter query,which in turn is
opened as a recordset in code. Opening the recordset produces run-time
error 3061 (too few parameters).

What is the fix?