Yup, that'll do it!
John Viescas
Sent from my iPad
On Jul 1, 2013, at 18:05, "dnwinberry" <winberry.doyce@roadsysinc.com> wrote:
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
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
