ACCESS FEED: [MS_AccessPros] Re: Error 3061 Too few parameters

Senin, 01 Juli 2013

[MS_AccessPros] Re: Error 3061 Too few parameters

John,

That makes sense. All fields are text except for Mat_labor, FET and Freight which are currency. I've updated the procedure to:

strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " & "'" & [Forms]![frmAddInvoiceDetail]![txtCustPO] & "', " & _
"VinMaster.Mat_Labor = " & [Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & ", " & _
"VinMaster.FET = " & [Forms]![frmAddInvoiceDetail]![txtFET] & ", " & _
"VinMaster.Freight = " & [Forms]![frmAddInvoiceDetail]![txtDelChg] & ", " & _
"VinMaster.BOM = " & "'" & [Forms]![frmAddInvoiceDetail]![cboBOM] & "', " & _
"WHERE (VinMaster.[Cust ID])= " & "'" & [Forms]![frmAddInvoiceDetail]![cboCustomer] & "' " & _
"AND (VinMaster.[Unit #]) Between '" & [Forms]![frmAddInvoiceDetail]![cboStartNo] & "'" & _
" And " & "'" & [Forms]![frmAddInvoiceDetail]![cboEndNo] & "';"

When I debug.print the SQL statement it returns:
UPDATE VinMaster SET VinMaster.CustPONo = 'K1857886', VinMaster.Mat_Labor = 22342, VinMaster.FET = 1340, VinMaster.Freight = 250, VinMaster.BOM = 'CWF28-34', WHERE (VinMaster.[Cust ID])= 'CTS' AND (VinMaster.[Unit #]) Between '465-3032' And '465-3063';

However, Now I'm getting runtime error 3144, "Syntax error in update statement so obviously I still don't have something right. One thing that comes to mind is parenthesis. When should they be used?

Doyce

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Doyce-
>
> You cannot put a parameter in SQL that you plan to Execute. You can,
> however, do direct substitution, like this:
>
> strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " &
> [Forms]![frmAddInvoiceDetail]![txtCustPO] & ", " & _
> "VinMaster.Mat_Labor = '" &
> [Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & "', " & _
> "VinMaster.FET = '" & [Forms]![frmAddInvoiceDetail]![txtFET] & "', "
> & _
> "VinMaster.Freight = '" & [Forms]![frmAddInvoiceDetail]![txtDelChg]
> & "', " & _
> "VinMaster.BOM = '" & [Forms]![frmAddInvoiceDetail]![cboBOM] & "' "
> & _
> "WHERE (((VinMaster.[Cust ID])= " &
> [Forms]![frmAddInvoiceDetail]![cboCustomer] & ") " & _
> "AND ((VinMaster.[Unit #]) Between " &
> [Forms]![frmAddInvoiceDetail]![cboStartNo] & " " & _
> "And " & [Forms]![frmAddInvoiceDetail]![cboEndNo] & "));"
>
> What I've done is supply the values directly in the SQL by resolving the
> control references in code BEFORE I pass it to the SQL parser. I've made
> some assumptions as to which fields might be number and which might be text.
> You may have to alter the insertion of ' characters accordingly.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
> Sent: Thursday, June 27, 2013 9:10 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Error 3061 Too few parameters
>
> I need some help with this error. I'm trying to update some records in a
> table using a button on a form and using controls on the form for parameters
> and data to update the records with. This form is unbound. Here is my code:
>
> Dim db As Database, strSQL As String
> Set db = CurrentDb
> strSQL = "UPDATE VinMaster SET VinMaster.CustPONo =
> [Forms]![frmAddInvoiceDetail]![txtCustPO], " & _
> "VinMaster.Mat_Labor =
> [Forms]![frmAddInvoiceDetail]![txtMatAndLabor], " & _
> "VinMaster.FET = [Forms]![frmAddInvoiceDetail]![txtFET], " & _
> "VinMaster.Freight = [Forms]![frmAddInvoiceDetail]![txtDelChg], " &
> _
> "VinMaster.BOM = [Forms]![frmAddInvoiceDetail]![cboBOM] " & _
> "WHERE (((VinMaster.[Cust
> ID])=[Forms]![frmAddInvoiceDetail]![cboCustomer]) " & _
> "AND ((VinMaster.[Unit #]) Between
> [Forms]![frmAddInvoiceDetail]![cboStartNo] " & _
> "And [Forms]![frmAddInvoiceDetail]![cboEndNo]));"
> db.Execute strSQL, dbFailOnError
> 'Debug.Print strSQL
> Set db = Nothing
>
> I get Error 3061 Too few parameters expected 8.
>
> Thanks for any help.
> Doyce
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>