"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:iz****************@newssvr11.news.prodigy.com ...
Beacher is on the right track. The syntax needs to change to handle a
value assigned to a Text data type field.
CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where
TRACKING = '" &
Me.txtOrderNumber & "';", dbfailonerror
Note the addition of the single quotes to delimit the value
Me.txtOrderNumber. Even though this may be a number, you are assigning it
to a text field and it will need to be treated as text. If the value can
have apostrophes in it, the syntax will need to be modified more.
--
Wayne Morgan
MS Access MVP
"N J" <bl*************@blueyonder.co.uk> wrote in message
news:VT*******************@fe3.news.blueyonder.co. uk... Hi,
CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where ID =
" & Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DELAYED " & "" & "", 0
When I change Where ID to Where TRACKING (another text field) I get the
error Data Type Mismatch in expression critera, or something like that
Help :(
ta
In addition to the comments you've already received, you also need to wary
of null values. In fact, I would take my time to dimension a strSQL
variable and build up the string before trying to execute it.
Dim strValue As String
Dim strSQL As String
strValue=Nz(Me.txtMyTextField ,"")
If Len(strSQL)>0 Then
strValue=AddQuotes(strValue, DoubleQuote)
strSQL="Update tblDelayedOrders Set DELAYED = True Where MyTextField=" &
strValue
...
Else
...
End If
And if adding those extra lines doesn't seem too bad you could also add
variables for dbs (Currentdb) so you can check for dbs.RecordsAffected,
which I gues should be one.
'Put the following in a separate module called modQuotes or similar:
Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum
Public Function AddQuotes(strValue, Q As QuoteType) As String
Dim strReturn As String
Select Case Q
Case QuoteType.SingleQuote
strReturn = Replace(strValue, "'", "''")
strReturn = "'" & strReturn & "'"
Case QuoteType.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"
Case Else
strReturn = strValue
End Select
AddQuotes = strReturn
End Function
