Too few parameters

Topic

New Reply

Good afternoon

MS Access 2007
I have the following select statement

strSql = “Select ” & sTable & “.* ”
strSql = strSql & “FROM ” & sTable & Chr(32)
strSql = strSql & “WHERE ” & sQFld & Chr(32)
strSql = strSql & “=” & Chr(32) & sFrm

This yields the following select statement with debug.print

Select tblcontacts.* FROM tblcontacts WHERE tblcontacts.ContactId = [forms]![frmContactEdit]![ContactId]

The create queryDef statement below works and behaves as expected
Set qdf = db.CreateQueryDef(“qry”, strSql)

However the attempt to open the recordset fails with error, too few parameters, expected 1
Set rs2 = db.OpenRecordset(“qry”, dbOpenDynaset)

When I stop the code I can manually select and execute the query successfully.

What am I doing wrong?

Regards

Geof

Reply | Quote

Replies

DAO operates at a lower level than Access; it doesn’t “know” about Access forms so it doesn’t recognize [forms]![frmContactEdit]![ContactId]

You can do the following:

strSql = “SELECT ” & sTable & “.*”
strSql = strSql & ” FROM ” & sTable
strSql = strSql & ” WHERE ” & sQFld
strSql = strSql & “=” & Forms!frmContactEdit!ContactID

Set rs2 = db.OpenRecordset(strSql, dbOpenDynaset)

This code doesn’t create a stored query, it opens the recordset directly on the SQL string, and the SQL string uses the literal value of ContactID at runtime.

Reply | Quote

Hello Hans
Thanks for the assistance.

In the last line of the query I would like to supply a variable that holds the expression [Forms]![FormName]![ControlName]

strSql = strSql & “=” & varName

The variable I am using reports correctly.
Debug.print varName reports correctly and
debug.print the var contents reports correctly.

I have tried creating a function and addressing that in the SQL

WHERE sFld = ” & functionName

I will have to try another approach.

Geof

DAO operates at a lower level than Access; it doesn’t “know” about Access forms so it doesn’t recognize [forms]![frmContactEdit]![ContactId]

You can do the following:

strSql = “SELECT ” & sTable & “.*”
strSql = strSql & ” FROM ” & sTable
strSql = strSql & ” WHERE ” & sQFld
strSql = strSql & “=” & Forms!frmContactEdit!ContactID

Set rs2 = db.OpenRecordset(strSql, dbOpenDynaset)

This code doesn’t create a stored query, it opens the recordset directly on the SQL string, and the SQL string uses the literal value of ContactID at runtime.

Reply | Quote

I get the impression you still have problems but you don’t tell us what they are.

Reply | Quote

I get the impression you still have problems but you don’t tell us what they are.

Hello Hans
The same problem, too few parameters.

I do not understand why concatenating the fully qualified reference, Forms!ForName!ContactId works but concatenating the varName doesnt.
Problems with the keyboard/chair interface I am afraid.

Here is some of my code
Thanks
Geof

———
code sample start
——-
Set db = currentdb()
Set rs = db.OpenRecordset(oFrm.RecordSource, dbOpenDynaset)
‘——————————————————–
‘locate the sourceTable of activeControl & then get pk
‘ passing sTable to findPrimaryKey
‘——————————————————–
sTable = rs(oFrm(sCtl).ControlSource).SourceTable
sPK = FindPrimaryKey(db.TableDefs(sTable))
sPK = Right(sPK, Len(sPK) – 1)

sField = rs(oFrm(sPK).ControlSource).SourceField

sQFld = sTable & “.” & sPK ‘primary Key field
sFrm = “[forms]!” & “[” & oFrm.Name & “]” & “!” & “[” & sPK & “]”

strSql = “Select ” & sTable & “.* ”
strSql = strSql & “FROM ” & sTable & Chr(32)
strSql = strSql & “WHERE ” & sQFld & Chr(32)
strSql = strSql & “=” & Chr(32) & sFrm

Set rs2 = db.OpenRecordset(“strSql”, dbOpenDynaset)
‘——-
code sample end
‘——-
db.openrecordset() errors out, Too few parameters

Reply | Quote

Your code will include the expression Forms!FormName!ContactId in the SQL, not its value. DAO does not recognize the expression Forms!FormName!ContactId because it doesn’t know anything about Access forms.

Try changing the line

sFrm = “[forms]!” & “[” & oFrm.Name & “]” & “!” & “[” & sPK & “]”

to

sFrm = oFrm.Controls(sPK)

Reply | Quote

Your code will include the expression Forms!FormName!ContactId in the SQL, not its value. DAO does not recognize the expression Forms!FormName!ContactId because it doesn’t know anything about Access forms.

Try changing the line

sFrm = “[forms]!” & “[” & oFrm.Name & “]” & “!” & “[” & sPK & “]”

to

sFrm = oFrm.Controls(sPK)

Hello Hans
You are a magician.
Thanks for the education. I now understand your point about DAO.

It works like a Swiss watch.

Cheers
Geof

Reply | Quote