Too Few Parameters Error

[Chris Thorne]

I have been having trouble with the error message, "Too
few parameters. Expected:1". I can see that others have
had the same trouble, but I still can't resolve mine. The
code is as follows :

Private Sub Change_Comment_Click()
Dim mydb As Database, myset1 As Recordset, mytempqry As
QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)

Set mytempqry = mydb.CreateQueryDef("TEMPQRY", "PARAMETERS
[Forms]![Frm_QStock Quarantine-RCR]![RecNo] Value; " & _
"SELECT [Tbl_QStock Pallets-Quarantine].[Q
Rec], [Tbl_QStock Pallets-Quarantine].[Pallet No] " & _
"FROM [Tbl_QStock Pallets-Quarantine] " & _
"Where ((([Tbl_QStock Pallets-Quarantine].[Q
Rec]) = [Forms]![Frm_QStock Quarantine-RCR]![RecNo])) " & _
"ORDER BY [Tbl_QStock Pallets-Quarantine].
[Pallet No];")
Set myset1 = mytempqry.OpenRecordset(dbOpenForwardOnly)

Do Until myset1.EOF
MsgBox myset1![Pallet No]
myset1.MoveNext
Loop
myset1.Close
mydb.Close

End Sub

The temporary query the code creates works fine if you
open the query. The parameter is based on a field in a
form I have created. If anyone has any suggestions, please
let me know. Cheers!

 

[Dan Artuso]

Hi,
Okay, here's how this stuff works.
First, when you're building a sql string, never put it directly in the function
call. Assign it to a variable first so that you can then issue a Debug.Print or
Msgbox to see what the evaluated string looks like.

For example:
Dim strSql As String

strSql = "Select somefield From sometable Where somefield = something"
Debug.Print strSql
mydb.CreateQueryDef("TEMPQRY",strSql)

Now if you do that with what you have below, you would see the string
exactly as you wrote it.
This not what you want. Your Where clause would be:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = [Forms]![Frm_QStock Quarantine-RCR]![RecNo]"

Obviously your [Q Rec] field would never contain the string "[Forms]![Frm_QStock Quarantine-RCR]![RecNo]"

You want Access to evaluate [Forms]![Frm_QStock Quarantine-RCR]![RecNo] and concatenate the VALUE into
your string so that it appears in the debug window as something like:

"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = 2"

In order for that to happen, you have to seperate the form reference from the string literal:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = " & [Forms]![Frm_QStock Quarantine-RCR]![RecNo]

Notice there are no quotes around the reference now. There are a few more rules you have to follow.
If the criteria is a string, you have to delimit it with single quotes, if it's a date you have to delimit with #.

So for string criteria:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = ' " & _
[Forms]![Frm_QStock Quarantine-RCR]![RecNo] & " ' "

the spaces are for clarity.

The above would evaluate to:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = '2'"

For Dates:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = #" & _
[Forms]![Frm_QStock Quarantine-RCR]![RecNo] & "#"

the above would evaluate to:
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = #2#"

So after all that, you would write your sql string like this:

strSql = "SELECT [Tbl_QStock Pallets-Quarantine].[Q Rec], " & _
"[Tbl_QStock Pallets-Quarantine].[Pallet No] " & _
"FROM [Tbl_QStock Pallets-Quarantine] " & _
"Where [Tbl_QStock Pallets-Quarantine].[Q Rec]) = " & _
Forms![Frm_QStock Quarantine-RCR]![RecNo] & _
" ORDER BY [Tbl_QStock Pallets-Quarantine].[Pallet No]"

Debug.Print strSql

--
HTH
Dan Artuso, Access MVP

I have been having trouble with the error message, "Too
few parameters. Expected:1". I can see that others have
had the same trouble, but I still can't resolve mine. The
code is as follows :

Private Sub Change_Comment_Click()
Dim mydb As Database, myset1 As Recordset, mytempqry As
QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)

Set mytempqry = mydb.CreateQueryDef("TEMPQRY", "PARAMETERS
[Forms]![Frm_QStock Quarantine-RCR]![RecNo] Value; " & _
"SELECT [Tbl_QStock Pallets-Quarantine].[Q
Rec], [Tbl_QStock Pallets-Quarantine].[Pallet No] " & _
"FROM [Tbl_QStock Pallets-Quarantine] " & _
"Where ((([Tbl_QStock Pallets-Quarantine].[Q
Rec]) = [Forms]![Frm_QStock Quarantine-RCR]![RecNo])) " & _
"ORDER BY [Tbl_QStock Pallets-Quarantine].
[Pallet No];")
Set myset1 = mytempqry.OpenRecordset(dbOpenForwardOnly)

Do Until myset1.EOF
MsgBox myset1![Pallet No]
myset1.MoveNext
Loop
myset1.Close
mydb.Close

End Sub

The temporary query the code creates works fine if you
open the query. The parameter is based on a field in a
form I have created. If anyone has any suggestions, please
let me know. Cheers!

 

[Jen]

Think I found the problem. Looks like you're missing
a '.' in your sql string.

Set mytempqry = mydb.CreateQueryDef("TEMPQRY", "PARAMETERS
[Forms]![Frm_QStock Quarantine-RCR]![RecNo] Value; " & _

should be...

Set mytempqry = mydb.CreateQueryDef("TEMPQRY", "PARAMETERS
[Forms]![Frm_QStock Quarantine-RCR]![RecNo].Value; " & _

Notice the period between [RecNo] and Value. Everything
else looks fine to me.

Regards,
Jen