Syntax error in Update statement

Prev: label formating
Next: Calculate conditiona total at bottom of continuous form.

From: Maresdd on 27 May 2010 15:53

---

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

From: Daniel Pineault on 27 May 2010 16:53

---

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" wrote:

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

From: DrGUI on 27 May 2010 16:56

---

Try changing strReason to the following:

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





"Maresdd" wrote:

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

From: Maresdd on 27 May 2010 17:23

---

thank you so much. That did the trick.

"DrGUI" wrote:

> Try changing strReason to the following:
>
> strReason = DLookup("[Reason]", "tblcnbreakdetails", "[ServiceNo] = " &
> Forms!frmcnbreak!ServiceNumber & " AND [Breakdate] = " &
> Forms!frmcnbreak!frmcnbreaksubform!break)
>
>
>
>
>
> "Maresdd" wrote:
>
> > 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
> >

 | 
Pages: 1
Prev: label formating
Next: Calculate conditiona total at bottom of continuous form.