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

Senin, 01 Juli 2013

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

Doyce-

Add this:

MsgBox "Update complete. " & db.RecordsAffected & " records updated."

John Viescas

Sent from my iPad

On Jul 1, 2013, at 17:37, "dnwinberry" <winberry.doyce@roadsysinc.com> 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

------------------------------------

Yahoo! Groups Links