Error 3061

[requeth]

Hello,

I'm receiving an error: "Run-time error '3061': Too few parameters.
Expected 1." from the following code. It works fine if I cut it off at
just the randnumber variable, but for data integrity I need to make
sure two fields match before I pull the Key field and save it. Am I
able to use double criteria in this sense? Or do I need to pull all
data with randnumber and save it to a temp table, and then query the
temp table with the second? It's longer but could be done if I have
to.

Thanks!

Set db = CurrentDb
'MsgBox keyvar
strSQL = "SELECT QCMS_PRIMARY.Key FROM QCMS_PRIMARY WHERE
QCMS_PRIMARY.RANDNUM = " & randnumber & " AND QCMS_PRIMARY.QA_SPCT_ID
= " & smoke & ""
'strSQL = "SELECT * FROM QCMS_PRIMARY WHERE RANDNUM = " &
randnumber
Set rs = db.OpenRecordset(strSQL)
'Exit if recordset has no records
If rs.EOF And rs.BOF Then
MsgBox "An error occured while saving. Please verify all fields
are filled out and try again. If the problem continues notify the
Process Team leader."
Exit Sub
End If
rs.MoveFirst
Do Until rs.EOF
strValue1 = rs!Key

'Do something with those values...

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

 

[storrboy]

Hello,

I'm receiving an error: "Run-time error '3061': Too few parameters.
Expected 1." from the following code. It works fine if I cut it off at
just the randnumber variable, but for data integrity I need to make
sure two fields match before I pull the Key field and save it. Am I
able to use double criteria in this sense? Or do I need to pull all
data with randnumber and save it to a temp table, and then query the
temp table with the second? It's longer but could be done if I have
to.

Thanks!

Set db = CurrentDb
'MsgBox keyvar
strSQL = "SELECT QCMS_PRIMARY.Key FROM QCMS_PRIMARY WHERE
QCMS_PRIMARY.RANDNUM = " & randnumber & " AND QCMS_PRIMARY.QA_SPCT_ID
= " & smoke & ""
'strSQL = "SELECT * FROM QCMS_PRIMARY WHERE RANDNUM = " &
randnumber
Set rs = db.OpenRecordset(strSQL)
'Exit if recordset has no records
If rs.EOF And rs.BOF Then
MsgBox "An error occured while saving. Please verify all fields
are filled out and try again. If the problem continues notify the
Process Team leader."
Exit Sub
End If
rs.MoveFirst
Do Until rs.EOF
strValue1 = rs!Key

'Do something with those values...

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

Other than missing brackets and ; I don't see anything obvious. I
assume the error occurs on the OpenRecordset method? Insert a
Debug.Print strSql before after building the string. Copy the printed
result from the immediate window and paste into the SQL view window of
a new query. Switch to design mode or preview. Many times the
offending field will be easier to identify here, you may also get a
different error about it's syntax to clue in on.

 

[Guest]

Am I able to use double criteria in this sense?
Yes.

Or do I need to pull all data with randnumber and save it to a temp table,...

No.

In order for your SQL statement to work as written, "smoke" must be numeric.
If this variable is text, then use the following form:

strSQL = "SELECT QCMS_PRIMARY.Key FROM QCMS_PRIMARY " _
& "WHERE QCMS_PRIMARY.RANDNUM = " & randnumber _
& " AND QCMS_PRIMARY.QA_SPCT_ID = '" & smoke & "'"

Note: I added some line continuation characters to the above line, so that
word wrap will not cause the line to break in an inappropriate place. For
clarity, the last line includes the following:

= ' " & smoke & " ' "

Alternatively, you can concatenate Chr(34) in place of the single quotes, as
in this example:

strSQL = "SELECT QCMS_PRIMARY.Key FROM QCMS_PRIMARY " _
& "WHERE QCMS_PRIMARY.RANDNUM = " & randnumber & " AND " _
& "QCMS_PRIMARY.QA_SPCT_ID = " & Chr(34) & smoke & Chr(34)

I actually prefer using Chr(34) myself.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________