UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Db.execute Help, Access 2016    
 
   
waitingroomz
post May 9 2019, 07:45 AM
Post#1



Posts: 81
Joined: 29-October 18



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.
Go to the top of the page
 
DanielPineault
post May 9 2019, 08:06 AM
Post#2


UtterAccess VIP
Posts: 6,671
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
waitingroomz
post May 9 2019, 08:25 AM
Post#3



Posts: 81
Joined: 29-October 18



My error handler is returning

Error # 3144
Syntax Error in UPDATE statement.
Go to the top of the page
 
waitingroomz
post May 9 2019, 08:33 AM
Post#4



Posts: 81
Joined: 29-October 18



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
Go to the top of the page
 
gemmathehusky
post May 12 2019, 04:32 AM
Post#5


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


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) & _

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 04:02 AM