Syntax error in Update statement

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
 
D

Daniel Pineault

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...
 
D

DrGUI

Try changing strReason to the following:

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

Maresdd

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

run a make table query using parameters in multi-select list box 7
HOW TO Simplify following code 2
Error 3144 - Syntax Error in UPDATE Statement 1
Update Syntax Error on SET 6
Check value before running Update Query 1
Syntax Error Need Help 3
Associating New Data with a SQL statement 1
Error 3144 - Syntax error in UPDATE Statement 6

Top