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

Senin, 01 Juli 2013

[MS_AccessPros] Re: Error 3061 Too few parameters

Ok. I've added this line to the code and got what I wanted.

MsgBox CStr(db.RecordsAffected) & " records updated.", vbInformation, "Records Updated"

Thanks for your help John!!

Doyce

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