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

Senin, 01 Juli 2013

[MS_AccessPros] Re: Error 3061 Too few parameters

John,

Kept working on this and looking at examples on the web and changed my "'" to """", added some parens around the where statement and removed a comma before the where statement and it works!

Is there an easy way to add a msgbox stating something like "update complete. XX records updated"?

Doyce

--- In MS_Access_Professionals@yahoogroups.com, "dnwinberry" <winberry.doyce@...> wrote:
>
> 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
> >
>