Update Record SQL

[D Willett]

I'm getting a syntax error with the following:

Code: Select all

Private Sub CmdUpdateRecord_Click()
DoCmd.RunSQL "Update tblEST SET EnquiryInfo WHERE JobID=me.txtjobid"
End Sub

The record already exists in tblEst ( JobID ), the SQL should add the contents of txtEnquiryInfo from this form to EnquiryInfo in tblEst for the existing record.

Kind Regards

[HansV]

Happy New Year, Dave!

1) You have to place Me.txtJobID outside the quotes.
2) You have to set EnquiryInfo to something. For example:

Code: Select all

Private Sub CmdUpdateRecord_Click()
    DoCmd.RunSQL "Update tblEST SET EnquiryInfo=Null WHERE JobID=" & Me.txtJobID
End Sub

to clear EnquiryInfo, or

Code: Select all

Private Sub CmdUpdateRecord_Click()
    DoCmd.RunSQL "Update tblEST SET EnquiryInfo=" & Chr(34) & Me.txtEnquiryInfo & Chr(34) & " WHERE JobID=" & Me.txtJobID
End Sub

to set EnquiryInfo to the value of a text box on your form. This assumes that EnquiryInfo is a text field and JobID is a number field.

[D Willett]

Cheers Hans

I can never work out SQL.... bugs me whenever I want to use it.

Thanks again :-)

(BTW --- Happy New Year )

[D Willett]

Hi Hans
Could do with a little more SQL help.

1. Update the field [Status] in tblEst to "A" Where JobID=" & Me.JobID
2. Update the field [A-Date] in tblEst to Date() Where JobID=" & Me.JobID

I tried a couple of attempts without success, see below

DoCmd.RunSQL "Update tblEST SET Status=A WHERE JobID=" & Me.JobID
DoCmd.RunSQL "Update tblEST SET Status=" & Chr(34) & A & Chr(34) & " WHERE JobID=" & Me.JobID

[HansV]

Your last SQL should work. Alternatively:

Code: Select all

DoCmd.RunSQL "Update tblEST SET Status='A' WHERE JobID=" & Me.JobID

The second is:

Code: Select all

DoCmd.RunSQL "Update tblEST SET [A-Date]=Date() WHERE JobID=" & Me.JobID

[D Willett]

Got it !!!

Found a useful link to study which should give me some help with future problems hopefully...

https://www.fmsinc.com/MicrosoftAccess/ ... SQL_Syntax

Once again thanks

[Rudi]

Got it !!!

Found a useful link to study which should give me some help with future problems hopefully...

https://www.fmsinc.com/MicrosoftAccess/ ... SQL_Syntax

Once again thanks

Here is another useful quick guide to SQL for Access (with syntax samples)
http://www.tutorialspoint.com/sql/sql-quick-guide.htm

[D Willett]

Thanks Rudi, saved to favourites for future reference.

Regards