Syntax error in Update statement

  • Thread starter Thread starter Maresdd
  • Start date Start date
M

Maresdd

I get a Run-time error 3144 with the following update statement. Could
somebody please help me with where I'm going wrong.

Private Sub UpdShifts_Click()
Dim strSQL As String
Dim strSQL2 As String
Dim Usern As String
Dim strReason As String

strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] =
Forms!frmcnbreak!ServiceNumber AND [Breakdate] =
Forms!frmcnbreak!frmcnbreaksubform!break")


Usern = fOSUserName()

DoCmd.SetWarnings False

strSQL = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes = " &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmCNbreaksubform]![Break]
And
(tblService_Date_and_Times.ServDate)<=[Forms]![frmcnbreak]![frmcnbreaksubform]![Return])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"

strSQL2 = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes =" &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmcnbreaksubform]![Break])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"


If IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL2

ElseIf Not IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL


End If

DoCmd.SetWarnings True


End Sub
 
One of the easiest ways to solve these types of problems is to set a
breakpoint after you define your SQL statements and then use the immediate
window to see the results of your VBA SQL Statement.

You'll either see a simply synthax error or you can copy the SQL Statement
into the standard query editor and troubleshoot there.

If you want further help troubleshooting this exact case, please post the
values of both strSQL and strSQL2.

Also, you use a number of Form control values, but you do not seemed to
validate that they actually are filled in. You really should test for each
one before blindly using them in a query. This could be the source of your
problem. Same thing goes for your DLookUp. You should test it to ensure
that it actually returned a value.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Maresdd said:
I get a Run-time error 3144 with the following update statement. Could
somebody please help me with where I'm going wrong.

Private Sub UpdShifts_Click()
Dim strSQL As String
Dim strSQL2 As String
Dim Usern As String
Dim strReason As String

strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] =
Forms!frmcnbreak!ServiceNumber AND [Breakdate] =
Forms!frmcnbreak!frmcnbreaksubform!break")


Usern = fOSUserName()

DoCmd.SetWarnings False

strSQL = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes = " &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmCNbreaksubform]![Break]
And
(tblService_Date_and_Times.ServDate)<=[Forms]![frmcnbreak]![frmcnbreaksubform]![Return])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"

strSQL2 = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes =" &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmcnbreaksubform]![Break])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"


If IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL2

ElseIf Not IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL


End If

DoCmd.SetWarnings True


End Sub
Click to expand...
 
Try changing strReason to the following:

strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] = " &
Forms!frmcnbreak!ServiceNumber & " AND [Breakdate] = " &
Forms!frmcnbreak!frmcnbreaksubform!break)
 
thank you so much. That did the trick.

DrGUI said:
Try changing strReason to the following:

strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] = " &
Forms!frmcnbreak!ServiceNumber & " AND [Breakdate] = " &
Forms!frmcnbreak!frmcnbreaksubform!break)





Maresdd said:
I get a Run-time error 3144 with the following update statement. Could
somebody please help me with where I'm going wrong.

Private Sub UpdShifts_Click()
Dim strSQL As String
Dim strSQL2 As String
Dim Usern As String
Dim strReason As String

strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] =
Forms!frmcnbreak!ServiceNumber AND [Breakdate] =
Forms!frmcnbreak!frmcnbreaksubform!break")


Usern = fOSUserName()

DoCmd.SetWarnings False

strSQL = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes = " &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmCNbreaksubform]![Break]
And
(tblService_Date_and_Times.ServDate)<=[Forms]![frmcnbreak]![frmcnbreaksubform]![Return])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"

strSQL2 = "UPDATE tblContracts INNER JOIN tblService_Date_and_Times ON
tblContracts.[Service No] = tblService_Date_and_Times.Service_Plan_ID SET
tblService_Date_and_Times.Hold = True, tblService_Date_and_Times.Notes =" &
strReason & " & "" "" & fOSUserName() & "" "" & Format(Now(),""Short Date"")"
& vbCrLf & _
"WHERE
(((tblService_Date_and_Times.ServDate)>=[Forms]![frmCNbreak]![frmcnbreaksubform]![Break])
AND
((tblService_Date_and_Times.Service_Plan_ID)=[Forms]![frmCNbreak]![ServiceNumber]));"


If IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL2

ElseIf Not IsNull(Forms!frmcnbreak!frmcnbreaksubform!Return) Then
DoCmd.RunSQL strSQL


End If

DoCmd.SetWarnings True


End Sub
Click to expand...
Click to expand...
 
Back
Top