Run-time error 3061 . . .

[Kurt]

I had an update query that would run fine when clicking a
button. But when I added one more statement (a parameter)
to the WHERE clause, I got a Run-time error 3061, "Too few
parameters expected. Expected 1."

Here's the statement I added which screws up the query and
causes the error:

"AND ((tblSchools.School)=[Enter School Name]));",

I recreated the query in design view using the same SQL
statement and it worked fine. But when used in code, I get
the error message.

Any ideas? - Kurt (SQL below)

------------------------------------------------
db.Execute "UPDATE tlkpTeacherGrades RIGHT JOIN
(tlkpGrades RIGHT JOIN " & _
"(tlkpDistricts INNER JOIN ((tblSchools INNER JOIN
(tblStudents LEFT JOIN tblTeachers " & _
"ON tblStudents.TeachID = tblTeachers.TeachID) " & _
"ON tblSchools.SchoolID = tblStudents.SchoolID) LEFT
JOIN tblScreenerTeacher " & _
"ON tblStudents.stuid = tblScreenerTeacher.stuid) " & _
"ON tlkpDistricts.DistrictID = tblSchools.DistrictID) "
& _
"ON tlkpGrades.GradeID = tblStudents.GradeID) " & _
"ON tlkpTeacherGrades.GradeID =
tblTeachers.TeacherGradeID " & _
"SET tblScreenerTeacher.TSSent = #" & dteDelDate & "# "
& _
"WHERE (((tblScreenerTeacher.TSSent) Is Null) AND
((tlkpGrades.Grade)<>'Preschool') " & _
"AND ((tblStudents.Status) Is Null) " & _
"AND ((tblSchools.School)=[Enter School Name]));",
dbFailOnError
--------------------------------------------------

Before adding the paramater statement, the query used to
end with

"AND ((tblStudents.Status) Is Null));", dbFailOnError

and it worked fine.

 

[Bob]

TRY:
DIM StrSql as String

strSql = "Update etc etc"
DoCmd.RunSQL strSql

Otherwise you have to create a QueryDef and then set the Parameters.

Bob

 

[Kurt]

TRY:

DIM StrSql as String

strSql = "Update etc etc"
DoCmd.RunSQL strSql

...
Bob

Thanks. That worked great.

Since I'm not using the db. feature, any idea how I can
adjust the following code which I used to show a message
to the user?

If db.RecordsAffected > 0 Then
'Tell user query was completed
MsgBox "Operation completed. Updated " &
db.RecordsAffected & " Records"
Else
MsgBox "There were no records to update."
End If

Set db = Nothing


Thanks. - Kurt

I had an update query that would run fine when clicking a
button. But when I added one more statement (a parameter)
to the WHERE clause, I got a Run-time error 3061, "Too few
parameters expected. Expected 1."

Here's the statement I added which screws up the query and
causes the error:

"AND ((tblSchools.School)=[Enter School Name]));",

I recreated the query in design view using the same SQL
statement and it worked fine. But when used in code, I get
the error message.

Any ideas? - Kurt (SQL below)

------------------------------------------------
db.Execute "UPDATE tlkpTeacherGrades RIGHT JOIN
(tlkpGrades RIGHT JOIN " & _
"(tlkpDistricts INNER JOIN ((tblSchools INNER JOIN
(tblStudents LEFT JOIN tblTeachers " & _
"ON tblStudents.TeachID = tblTeachers.TeachID) " & _
"ON tblSchools.SchoolID = tblStudents.SchoolID) LEFT
JOIN tblScreenerTeacher " & _
"ON tblStudents.stuid = tblScreenerTeacher.stuid) " & _
"ON tlkpDistricts.DistrictID = tblSchools.DistrictID) "
& _
"ON tlkpGrades.GradeID = tblStudents.GradeID) " & _
"ON tlkpTeacherGrades.GradeID =
tblTeachers.TeacherGradeID " & _
"SET tblScreenerTeacher.TSSent = #" & dteDelDate & "# "
& _
"WHERE (((tblScreenerTeacher.TSSent) Is Null) AND
((tlkpGrades.Grade)<>'Preschool') " & _
"AND ((tblStudents.Status) Is Null) " & _
"AND ((tblSchools.School)=[Enter School Name]));",
dbFailOnError
--------------------------------------------------

Before adding the paramater statement, the query used to
end with

"AND ((tblStudents.Status) Is Null));", dbFailOnError

and it worked fine.

.