UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Too Few Parameters Expected 1    
 
   
foxtrot123
post Dec 27 2011, 08:11 PM
Post #1

UtterAccess Addict
Posts: 149
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?
Go to the top of the page
 
+
Jeff B.
post Dec 27 2011, 08:59 PM
Post #2

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
Check Access HELP for .Parameter -- you'll need to tell VBA what the value of the parameter is before using it.
Go to the top of the page
 
+
datAdrenaline
post Dec 27 2011, 09:02 PM
Post #3

UtterAccess Editor
Posts: 15,974
From: Northern Virginia, USA
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.
Go to the top of the page
 
+
foxtrot123
post Jan 17 2012, 02:16 PM
Post #4

UtterAccess Addict
Posts: 149
Thank you. The Eval function did the trick.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 24th May 2013 - 01:32 AM