3144 Syntax Error In Update Statement
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss
Runtime Error 3144 Syntax Error In Update
the workings and policies of this site About Us Learn more about syntax error in update statement access Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow syntax error in update statement c# Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each
Syntax Error In Update Statement Vba
other. Join them; it only takes a minute: Sign up Access 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
Syntax Error In Update Statement Oledb
in the Training_Record reference records in Staff and Courses. When a staff member or course is added, 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_Reco
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 29 Sep 2016 14:11:10 GMT by s_hv997 (squid/3.5.20)
2006. Guest The field 'TRADERSIGNOFFID' in 'tblApp_TraderSignOff' table is a 'Autonumber' and 'Long Integer' data http://www.pcreview.co.uk/threads/ms-access-vba-run-time-error-3144-error-in-update-statement.2768917/ type. Somehow, I can't seem to correct SQL syntax error http://www.vbforums.com/showthread.php?676439-Access-Run-time-error-3144-Syntax-error-in-UPDATE-statement for this VBA update query in 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 syntax error 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 & """ SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF] strSQL = strSQL & """, TRADE_ID= """ & Me![TxtTRADE_ID] strSQL = strSQL & """, ABACUS_ID= syntax error in """ & 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] 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
- Syntax error in UPDATE statement If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 5 of 5 Thread: [Access] Run-time error '3144': - Syntax error in UPDATE statement Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Apr 3rd, 2012,04:46 PM #1 hydrakiller4000 View Profile View Forum Posts Thread Starter Member Join Date Oct 2010 Posts 56 [Access] Run-time error '3144': - Syntax error in UPDATE statement Hello, everyone! I'm running an update query programmatically, and I'm receiving a syntax error for some reason. I can't seem to figure out what's wrong. Here's my code: Code: Dim db As Database Set db = CurrentDb() db.Execute "UPDATE tblBins SET datRepairDate = null, datClaimDate = null, " & _ "txtRepairTech = " & vbNullString & ", txtTypeandSerial = " & vbNullString & ", " & _ "txtAsset = " & vbNullString & ", " & _ "txtPart1andFRU = " & vbNullString & ", txtPart2andFRU = " & vbNullString & ", " & _ "txtPart3andFRU = " & vbNullString & ", txtPart4andFRU = " & vbNullString & ", " & _ "txtPart5andFRU = " & vbNullString & ", memoProblemandTroubleshooting = " & vbNullString & ", " & _ "intHEAT = null, txtLenovoClaimID = " & vbNullString & ", txtStatus = " & vbNullString & ", " & _ "txtTrackingNumber1 = " & vbNullString & ", txtShortClaimNumber1 = " & vbNullString & ", " & _ "txtTrackingNumber2 = " & vbNullString & ", txtShortClaimNumber2 = " & vbNullString & ", " & _ "txtTrackingNumber3 = " & vbNullString & ", txtShortClaimNumber3 = " & vbNullString & ", " & _ "txtTrackingNumber4 = " & vbNullString & ", txtShortClaimNumber4 = " & vbNullString & ", " & _ "txtTrackingNumber5 = " & vbNullString & ", txtShortClaimNumber5 = " & vbNullString & ", " & _ "boolComplete = False " & _ "WHERE ((([tblBins].binID)='" & Me.binID & "'));" Everything is a String except for intHEAT, datRepairDate, datClaimDate, and boolComplete. Those variables are of the data types Integer, Date, Date, and Boolean, respectively. I've tried a lot of solutions and suggestions online, but I'm not sure what the syntax error is. Does anyone have any suggestions? Thanks in advance for any help! Reply With Quote Apr 4th, 2012,04:26 AM #2 si_the_geek View Profile View Forum Posts Super Moderator Join Date Jul 2002 Location Bristol, UK Posts 38,273 Re: [Access] Run-time error '3144': - Syntax error in UPD