Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Records _ Db.execute Help

Posted by: waitingroomz May 9 2019, 07:45 AM

Good morning all,

Hoping I can get an answer from one of you guys.

I have a text string that I need to update a specific record with that is generated by an input box.

The input box when closed pushes the text entry to strUpdated and I need it to update to the underlying record from the form that called it.

I have it set up as:

CODE
If Not IsNull(strUpdated) Then CurrentDb.Execute "UPDATE EmailAddress " & _
"FROM tblPatients SET EmailAddress=" & strUpdated & " WHERE ID =" & Me.ID, dbFailOnError


I am sure I'm not understanding the .Execute correctly and would appreciate your help.

Posted by: DanielPineault May 9 2019, 08:06 AM

The issue is that when dealing with text data types you need to surround the value in single quotes, so try something more along the lines of:

CODE
    If Not IsNull(strUpdated) Then
        CurrentDb.Execute "UPDATE EmailAddress " & _
                          "FROM tblPatients " & _
                          "SET EmailAddress='" & strUpdated & "' " & _
                          "WHERE ID =" & Me.ID, dbFailOnError
    End If


and here's a slight modification on the above which enable you to check how many records were actually updated, should you wish to know this.
CODE
    Dim db                    As DAO.Database

    If Not IsNull(strUpdated) Then
        Set db = CurrentDb
        db.Execute "UPDATE EmailAddress " & _
                   "FROM tblPatients " & _
                   "SET EmailAddress='" & strUpdated & "' " & _
                   "WHERE ID =" & Me.ID, dbFailOnError
        Call MsgBox(db.RecordsAffected & "records were updated", vbInformation Or vbOKOnly)
    End If
    If Not db Is Nothing Then Set db = Nothing

Posted by: waitingroomz May 9 2019, 08:25 AM

My error handler is returning

Error # 3144
Syntax Error in UPDATE statement.

Posted by: waitingroomz May 9 2019, 08:33 AM

Corrected code that worked.

Thanks for your help!

CODE
    If Not IsNull(strUpdated) Then
        Set db = CurrentDb
        db.Execute "UPDATE tblPatients " & _
                   "SET EmailAddress='" & strUpdated & "' " & _
                   "WHERE ID =" & Me.ID, dbFailOnError
        Call MsgBox(db.RecordsAffected & "records were updated", vbInformation Or vbOKOnly)
    End If

Posted by: gemmathehusky May 12 2019, 04:32 AM

as an alternative, I use chr(34) for a " character, which I find more readable.

"SET EmailAddress='" & strUpdated & "' " & _

becomes

"SET EmailAddress=" & chr(34) & strUpdated & chr(34) & _