?
Solved

Too few parameters...expected 5

Posted on 2009-12-17
10
Medium Priority
?
391 Views
Last Modified: 2013-11-28
I'm using the INSERT INTO statement in Access 2007, but I am getting the error "too few parameters-expected 5".  It looks to me like I have 5!  Can anyone see the problem?
Private Sub submitButton_Click()
   
   'Insert new record into TBL_Office_Visits
            
   Dim strSQL As String
         
   strSQL = "INSERT INTO TBL_Office_Visits_Students (AutoNumber, Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "(Me.AutoNumber.Value, Date, Time, Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
               
   CurrentDb.Execute strSQL, dbFailOnError
   DoCmd.SetWarnings False
     
   DoCmd.Close acForm, "FRM_Returning_Student_Detail", acSaveYes
   DoCmd.OpenForm "FRM_Thank_You", acNormal
End Sub

Open in new window

0
Comment
Question by:mkelly2384
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 14

Expert Comment

by:svgmuc
ID: 26073211
SQL statements usually need a semicolon at the end. Don't know if Access is anal about that.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26073252
probably you should remove this

AutoNumber

strSQL = "INSERT INTO TBL_Office_Visits_Students (Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "(Date, Time, Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26073267
and are you sure Date & Time is valid function is Access?

& "(Date, Time, Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 61

Expert Comment

by:HainKurt
ID: 26073283
it should be

& "(Date, Time, Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
-->
& "(Date(), Time(), Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26073328
The Date and Time functions require parentheses if not used in VBA.  Since you have Date and Time, in quotations, they are beeing called by SQL, not VBA so they need the parentheses.  I agree about the autonumber field.
"INSERT INTO TBL_Office_Visits_Students (Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "( Date(), Time(), Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value)"
0
 

Author Comment

by:mkelly2384
ID: 26073359
Hi thenelson,

So when I copied and pasted that line in that you wrote, it said I have too few parameters--expected 2.  No luck so far.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 26073447

if ReasonForVisit, and EmployeeBeingVisited are Both Text type data, use this


"INSERT INTO TBL_Office_Visits_Students (Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "( Date(), Time(), '" & Me.reasonForVisitComboBox.Value &"', '" & Me.employeeBeingVisitedComboBox.Value &"')"
0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 400 total points
ID: 26073465
I also missed Me.reasonForVisitComboBox.Value, Me.employeeBeingVisitedComboBox.Value.  Those are VBA values and should be outside the quotation marks:
"INSERT INTO TBL_Office_Visits_Students (Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "(" &  Date & "," & Time & "," & Me.reasonForVisitComboBox.Value& "," &  Me.employeeBeingVisitedComboBox.Value & ");"

To debug a generated query string, place a code break on the line DoCmd.RunSQL or CurrentDB.Execute.  While the code in stopped, type ?strSQL (or whatever the string is called) in the immediate window and press enter to see what was generated. Just by looking at it you might see the problem. You can also copy and paste it into the query builder to further debug it.


BTW:  You have
DoCmd.SetWarnings False
in the wrong place and it is not needed for
CurrentDb.Execute
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 400 total points
ID: 26073487
try this

strSQL = "INSERT INTO TBL_Office_Visits_Students (AutoNumber, Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "(Me.AutoNumber.Value, Date(), Time(), '" & Me.reasonForVisitComboBox.Value & "', '" & Me.employeeBeingVisitedComboBox.Value & "')"
0
 

Author Comment

by:mkelly2384
ID: 26073500
capricorn1--it worked!

Thank you so much, everyone!  I will award points appropriately.  Sure appreciate it.
   strSQL = "INSERT INTO TBL_Office_Visits_Students (Visit_Date, Visit_Time, ReasonForVisit, EmployeeBeingVisited) VALUES " _
       & "( Date(), Time(), '" & Me.reasonForVisitComboBox.Value & "', '" & Me.employeeBeingVisitedComboBox.Value & "')"

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question