Help! Run-time Error '3144' Syntax error in UPDATE statement (1 Viewer)

irunergoiam · Feb 2, 2012

I'm getting a message: Run-time Error '3144' Syntax error in UPDATE statement.

I'm not sure why it won't run - I'm sure the error is taking place somewhere between the keyboard and my chair. When I run this from an On Click event, I get this error and when I go to the code, the last line DoCmd.RunSQL strSQL is highlighted in yellow.

Any ideas or gentle nudges in the right direction would be most appreciated.

Dim strSQL As String

strSQL = "UPDATE [tblOSHWALabRequests] SET [tblOSHWALabRequests].[UpdateMedHoldDispo] = '" & cmbMedHoldDispo & "', [tblOSHWALabRequests].[UpdateMedHoldDate] = #" & unbMedHoldDispoDate & "#, [tblOSHWALabRequests].[UpdateMedHoldBy] = '" & unbMedHoldDispoBy & "', [tblOSHWALabRequests].[LabReqStatus] = " ' & cmbMedHoldDispo & '""
strSQL = strSQL & "WHERE (([tblOSHWALabRequests].[ApplicantID])=[Forms]![OSHWA_MRO_Disposition]![unbAppID]) AND (([tblOSHWALabRequests].[Req])=[Forms]![OSHWA_MRO_Disposition]![unbReq]));"
DoCmd.RunSQL strSQL

vbaInet · Feb 2, 2012

What do you get in the Immediate Window when you Debug.Print strSQL?

Kiwiman · Feb 2, 2012

Howzit

There does not appear to be a space between the end of the SET
part and the WHERE key word.

Edit: Also apostrophes and quote mark around wrong way on last set

Code:

 [tblOSHWALabRequests].[LabReqStatus] = '"  & cmbMedHoldDispo & "'" 
strSQL = strSQL & " WHERE

irunergoiam · Feb 2, 2012

Thank you Kiwiman!! You were right on both counts. This site is hands down the best Access resource on the web. Everyone is so kind, patient, and mentoring. Thanks for helping not only with this question, but with expanding my understanding of how the UPDATE statement works.

wanamaa · Jul 20, 2015

Hello all,

I am running into a very similar issue. I'm learning Access as I go. I have been working with VBA for over a year and a half, but Access throws me through a loop :banghead:. Google has been my friend lately

Anyway, here is my code below that produces the '3144' error pointing at the "WHERE" keyword string:

Code:

CurrentDb.Execute "UPDATE rmaList" & _
        " SET rmanum=" & Me.numRma & _
        ", cameraser=" & Me.numSerial & _
        ", modelNum=" & Me.cboModel & _
        ", lensSwap=" & Me.cboLens.Tag & _
        ", lensSerOld=" & Me.numLensOld & _
        ", lensSerNew=" & Me.numLensNew & _
        ", PartSwap=" & Me.cboPart.Tag & _
        ", PartSerOld='" & Me.numPartOld & "'" & _
        ", PartSerNew='" & Me.numPartNew & "'" & _
        ", txtNotes='" & Me.txtNotes & "'" & _
        ", shipped=(#" & Me.dateShip & "#)" & _
        " WHERE rmanum=" & Me.numRma.Tag

Prior to running this code, I fill the tag for numRMA with it's value (found this in a tutorial I based my userform on). I'm stuck trying to figure out where I've got an issue; I think my apostrophe and quotes are right, spacing looks right, not sure where else I'm having issues...any help/insight is greatly appreciated.

spikepl · Jul 20, 2015

This is how you debug it - read carefully and follow the recipe: http://www.baldyweb.com/immediatewindow.htm

wanamaa · Jul 20, 2015

@spikepl

Thank you so much, that helps narrow down where the issue was!!! I was wondering about how to get the immediate window (never have had to use it in Visual Studio yet). Turns out my lensSwap and PartSwap didn't like having empty values (they are boolean type), which was causing the error. After adding in some error checking I was able to modify by SQL based on their True/False values. Definitely bookmarking that site you sent me for debug print

Help! Run-time Error '3144' Syntax error in UPDATE statement (1 Viewer)

irunergoiam

Registered User.

vbaInet

AWF VIP

Kiwiman

Registered User

irunergoiam

Registered User.

wanamaa

New member

spikepl

Eledittingent Beliped

wanamaa

New member

Similar threads

Users who are viewing this thread