Full Version: Too Few Parameters Expected 1
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
foxtrot123
Dec 27 2011, 08:11 PM
When I try to run an INSERT INTO sql, I get "Too few parameters expected 1." But the SQL runs flawlessly when I paste it into the query designer and run it there (in fact, that's how I got the statement in the first place). The query also runs fine when I reference it as a stored procedure, like:

DoCmd.OpenQuery "qappResponses"

The query is executed after the user clicks a "Close" button on a form, frmSubjects (which is referenced in the query), which saves the form (Me.Dirty = False) and sets it to invisible (Me.Visible = False).

This works:
CODE
DoCmd.OpenQuery "qappResponses"

But its SQL equivalent (below) gives the parameter error. Although I thought the Where clause was the problem, when I removed it I still get the error, suggesting it's happening in the first two lines or so. SubjectID, SurveyID, and QstnID are all numeric.

CODE
strSQL = "INSERT INTO tblResponses ( SubjectID, SurveyID, QstnID, Rspns ) " & _
"SELECT DISTINCTROW [Forms]![frmSubjects]![SubjectID] AS SubjectID, tblSurveysQuestions.SurveyID, tblQuestions.QstnID, tblQuestions.RspnsDefault " & _
"FROM tblQuestions INNER JOIN tblSurveysQuestions ON tblQuestions.QstnID = tblSurveysQuestions.QstnID " & _
"WHERE tblSurveysQuestions.SurveyID=" & 15 & _
"AND tblQuestions.RspnsType<>" & 10

CurrentDb.Execute strSQL, dbFailOnError


Any ideas?
Jeff B.
Dec 27 2011, 08:59 PM
Check Access HELP for .Parameter -- you'll need to tell VBA what the value of the parameter is before using it.
datAdrenaline
Dec 27 2011, 09:02 PM
To learn more about the 'why', then check out the post found here and the WIKI article here.

The nutshell version is this, CurrentDb does not have visibility of Access objects (like your form reference parameter), where as DoCmd.OpenQuery (and .RunSQL) does, so typically you would simply concatenate your parameter into the string in VBA.

CODE
strSQL = "INSERT INTO tblResponses ( SubjectID, SurveyID, QstnID, Rspns ) " & _
"SELECT DISTINCTROW '" & Forms("[frmSubjects").Controls("SubjectID") & "' AS SubjectID, tblSurveysQuestions.SurveyID, tblQuestions.QstnID, tblQuestions.RspnsDefault " & _
"FROM tblQuestions INNER JOIN tblSurveysQuestions ON tblQuestions.QstnID = tblSurveysQuestions.QstnID " & _
"WHERE tblSurveysQuestions.SurveyID=" & 15 & _
"AND tblQuestions.RspnsType<>" & 10

CurrentDb.Execute strSQL, dbFailOnError


Or ... you can wrap your form reference in the Eval() function ...

CODE
strSQL = "INSERT INTO tblResponses ( SubjectID, SurveyID, QstnID, Rspns ) " & _
"SELECT DISTINCTROW Eval('[Forms]![frmSubjects]![SubjectID]') AS SubjectID, tblSurveysQuestions.SurveyID, tblQuestions.QstnID, tblQuestions.RspnsDefault " & _
"FROM tblQuestions INNER JOIN tblSurveysQuestions ON tblQuestions.QstnID = tblSurveysQuestions.QstnID " & _
"WHERE tblSurveysQuestions.SurveyID=" & 15 & _
"AND tblQuestions.RspnsType<>" & 10

CurrentDb.Execute strSQL, dbFailOnError


I encourage you to read the referenced post and article to learn more.
foxtrot123
Jan 17 2012, 02:16 PM
Thank you. The Eval function did the trick.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.