none
Update Table VBA runtime error 3061 Too Few Parameters. Expected 1

    Question

  • I am attempting to write a basic table update form. I currently have:

     CurrentDb.Execute "INSERT INTO Users(AccountNumber, LastName, FirstName, PhoneNumber, TotalRentals) " & _
            " VALUES (" & Me.txtID & ",'" & Me.txtLN & "','" & _
            Me.txtFN & "','" & Me.txtPN & "','" & Me.txtRent & "')"

    When I attempt to add a user I get a runtime error 3061. Am I just missing something really simple?

    Wednesday, March 19, 2014 2:24 PM

Answers

  • Change your code to:

    Dim strSQL As String
      
    strSQL = "INSERT INTO Users(AccountNumber, LastName, FirstName, PhoneNumber, TotalRentals) " & _
      " VALUES ('" & Me.txtID & "', '" & _
      Replace(Me.txtLN, "'", "''") & "', '" & _
      Replace(Me.txtFN, "'", "''") & "', '" & _
      Me.txtPN & "'," & Me.txtRent & ")"
      
    Debug.Print strSQL
      
    CurrentDb.Execute strSQL, dbFailOnError

    If you still get error, post the error details plus the String printed in the Immediate/Debug window by the Debug statement above.  Once the code works correctly, comment out the Debug statement.


    Van Dinh



    • Marked as answer by longbr83 Wednesday, March 19, 2014 5:25 PM
    • Edited by Van Dinh Thursday, March 20, 2014 1:54 AM Typos
    Wednesday, March 19, 2014 3:33 PM
  • >> I still wish I knew what was wrong with my original code.<<

    One or both of these 2 reasons:

    1.  [AccountNumber] is a Text Field but your code passes the value (in the value list) as if it were a Numeric Field, i.e. without the Text/String delimiters.

    2.  [TotalRentals] is a Numeric Field but your code passes the value as if it were a Text Field, i.e with the Text/String delimiters.

     


    Van Dinh

    Wednesday, March 19, 2014 8:26 PM

All replies

  • Probably your input has names like O'Neil, O'Connor, which might throw the syntax right out of the window, try this..

    CurrentDb.Execute "INSERT INTO Users(AccountNumber, LastName, FirstName, PhoneNumber, TotalRentals) " & _
    				  " VALUES (" & Me.txtID & ", " & Chr(34) & Me.txtLN & Chr(34) & ", " & Chr(34) & Me.txtFN & _
    				  ", " & Chr(34) & Me.txtPN & Chr(34) & ", " & Chr(34) & Me.txtRent & Chr(34) & ")"


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Wednesday, March 19, 2014 2:42 PM
  • I tried that and it now gives me a runtime error 3075 "Syntax error (missing operator) in query expression "Kate, 4057856321", "3")'.

    Those are FirstName, PhoneNumber, and TotalRentals. They give me the same missing operator values for any information I put in those fields.

    Wednesday, March 19, 2014 2:51 PM
  • Try using DoCmd.RunSql like this -- also note that I am using the Replace VBA function for replacing any "'" apostrophe's with double "''" apostrophe's since "'" is a reserved symbol in VBA and most programming languages.  If there are no apostrophe's -- it won't replace anything -- thus you can use it on all your name values.  Oh, and instead of using "Values..." I use "Select ..."  It's more consistent with Sql.  Also, if txtRent is numeric (no dashes or other alpha characters) you probably shouldn't delimit it with the "'" single quotes (which quotes/apostrophe's same symbol).

    Dim strSql As String
    
    strSql = "INSERT INTO Users(AccountNumber, LastName, FirstName, PhoneNumber, TotalRentals) " _
            & "Select " & Me.txtID & ",'" & Replace(Me.txtLN, "'", "''") & "','" &  Replace(Me.txtFN, "'", "''") & "','" & Me.txtPN & "','" & Me.txtRent & "'"
    
    DoCmd.RunSql strSql
    
    


    Rich P

    Wednesday, March 19, 2014 3:09 PM
  • What is the data-type of [AccountNumber] in the Table?

    What is the data-type of [TotalRentals] in the Table?

    It seems a bit odd that the data-type of [TotalRentals] is of String data-type according to your SQL?


    Van Dinh

    Wednesday, March 19, 2014 3:09 PM
  • Everything is short text except TotalRentals. TotalRentals is number.
    Wednesday, March 19, 2014 3:21 PM
  • Change your code to:

    Dim strSQL As String
      
    strSQL = "INSERT INTO Users(AccountNumber, LastName, FirstName, PhoneNumber, TotalRentals) " & _
      " VALUES ('" & Me.txtID & "', '" & _
      Replace(Me.txtLN, "'", "''") & "', '" & _
      Replace(Me.txtFN, "'", "''") & "', '" & _
      Me.txtPN & "'," & Me.txtRent & ")"
      
    Debug.Print strSQL
      
    CurrentDb.Execute strSQL, dbFailOnError

    If you still get error, post the error details plus the String printed in the Immediate/Debug window by the Debug statement above.  Once the code works correctly, comment out the Debug statement.


    Van Dinh



    • Marked as answer by longbr83 Wednesday, March 19, 2014 5:25 PM
    • Edited by Van Dinh Thursday, March 20, 2014 1:54 AM Typos
    Wednesday, March 19, 2014 3:33 PM
  • That did the trick. Thank you! I still wish I knew what was wrong with my original code.
    Wednesday, March 19, 2014 5:26 PM
  • >> I still wish I knew what was wrong with my original code.<<

    One or both of these 2 reasons:

    1.  [AccountNumber] is a Text Field but your code passes the value (in the value list) as if it were a Numeric Field, i.e. without the Text/String delimiters.

    2.  [TotalRentals] is a Numeric Field but your code passes the value as if it were a Text Field, i.e with the Text/String delimiters.

     


    Van Dinh

    Wednesday, March 19, 2014 8:26 PM