3144 Syntax Error In Update
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this syntax error in update statement site About Us Learn more about Stack Overflow the company Business Learn syntax error in update statement access more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x syntax error in update statement c# Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Access syntax error in update statement vba VBA: SQL query causes UPDATE syntax error up vote 2 down vote favorite I have a database with linked tables- Staff, Courses and Training_Record. Each staff member has a numeric primary key, as does each course and each entry in the Training_Record table. The Staff_ID and Course_ID in the Training_Record reference records in Staff and Courses. When a staff member or course is added,
Syntax Error In Update Statement Oledb
the Training_Record (fields: Staff_ID, Course_ID, Date_Taken, Notes) has staff,course records inserted- so adding staff member 1 would insert records (1,1,,,), (1,2,,,) etc, adding course 8 would insert records (1,8,,,), (2,8,,,) and so on. This works. I then have a form to record training. The user selects the course, enters the date and selects staff members from a listbox. I have a save button which triggers VBA code. The date and course are pulled from the boxes and I loop round the listbox, concatenating selected staff members into a string. This all works and a message box displays, verifying that. Then, an update SQL query should be run, updating the Training_Record. The problem I have is with the SQL update. I have an update query that will work in the SQL query editor, though it uses written in variables: UPDATE Training_Record SET Date_Taken = '12/12/12' WHERE Staff_ID IN (1,2,3,4,5) AND Course_ID = 4 This updates the Training_Record to show that staff 1,2,3,4 and 5 took course 4 on 12/12/12. However, in VBA this will not work. This is my SQL query in VBA: strSQL = "UPDATE Training_Record" _ & "SET Date_Tak
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 29 Sep 2016 21:05:28 GMT by s_hv1000 (squid/3.5.20)
2006. Guest The field 'TRADERSIGNOFFID' in 'tblApp_TraderSignOff' table is a 'Autonumber' and 'Long Integer' data type. Somehow, I can't seem to correct SQL syntax error for this VBA update query in http://www.pcreview.co.uk/threads/ms-access-vba-run-time-error-3144-error-in-update-statement.2768917/ MS Access. Can U spot what I am doing wrong and correct it? Many thanks in Advance... Brenda xxx '--------------------------------------------------------------- Private Sub CmdSaveChangesTSO_Click() Dim H As Database, rsCust As Recordset Set H = CurrentDb Dim strSQL As String If IsNull(CboTRADER_SIGNOFF_ID.Value) Then MsgBox "Please select a 'Trader Sign Off' record from the Combo list first." Exit Sub Else strSQL = "UPDATE tblApp_TraderSignOff """ ' table text strSQL = strSQL & syntax error """ SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF] strSQL = strSQL & """, TRADE_ID= """ & Me![TxtTRADE_ID] strSQL = strSQL & """, ABACUS_ID= """ & Me![TxtABACUS_ID] strSQL = strSQL & """, TRADE_DETAILS= """ & Me![TxtTRADE_DETAILS] strSQL = strSQL & """, TRADER_ID= """ & Me![TxtTRADER_ID] strSQL = strSQL & """, STAMM_SHORT_NAME= """ & Me![TxtSHORT_NAME] strSQL = strSQL & """, DETAILS_OF_ERROR= """ & Me![TxtDETAILS_OF_ERROR] strSQL = strSQL & """, TRADER_COST_CENTRE= """ & Me![TxtTRADERCC] syntax error in strSQL = strSQL & """, CURRENCY_CODE= """ & Me![TxtCURRENCY_CODE] strSQL = strSQL & """, BASE_ERROR_FINANCE_COST= """ & Me![TxtEFC_AMT] strSQL = strSQL & """, EFC_USD_EQUIV= """ & [TxtEFC_AMT_USD] strSQL = strSQL & """, EFC_BREAKDOWN= """ & Me![TxtEFC_BREAKDOWN] strSQL = strSQL & """, TRADERSIGNOFFSTATUS= """ & Me![TxtSIGNOFF_STATUS] strSQL = strSQL & """, USERID= """ & Me![TxtUserID] strSQL = strSQL & """, TIMESTAMP= """ & Now() strSQL = strSQL & """ WHERE TRADERSIGNOFFID =""" & Me![CboTRADER_SIGNOFF_ID] H.Execute strSQL MsgBox "Changes have been saved." End If End Sub '--------------------------------------------------------------- , Aug 15, 2006 #1 Advertisements Guest Guest I may be missing something, but I'm kinda confused about your reasoning for putting """ in all your statements. strSQL & "SET EFC_CASE_REF= " & Me![TxtEFC_CASE_REF] should work fine, it may be destorting your string. I'm assuming your updating off of a form because your basing it on a button click. Sometimes Me! doesn't work right in grabing the data (in my experiences). Checking the name of your field names would be another good place to start, and to make sure your data types match up. Maybe change your function to msgbox(Me![TxtEFC_CASE_REF]) to make sure your data is pulling out correctly also, and do that with each field name to troubleshoot whe