SQL Not Updating

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

For whatever reason this isn't UPDATING...
This gives me a 3144 Error
Dim UPSQL As String
DoCmd.SetWarnings False
UPSQL = "UPDATE tblPass SET tblPass.Type = " & Data & " " & _
"WHERE tblPass.PassID = Forms!frmSwitch!TxtPassID;"
DoCmd.RunSQL (UPSQL)
DoCmd.SetWarnings True

I tried this also...
This does nothing...
Dim UPSQL As String
DoCmd.SetWarnings False
UPSQL = "UPDATE tblPass SET tblPass.Type = " & Data & " " & _
"WHERE tblPass.PassID = Forms!frmSwitch!TxtPassID;"
DoCmd.RunSQL (UPSQL)
DoCmd.SetWarnings True

Data is a word....

Any help appreciated.
Thanks
DS
 
Since Data is text, its value needs to be enclosed in quotes:

UPSQL = "UPDATE tblPass SET tblPass.Type = """ & Data & """ " & _
"WHERE tblPass.PassID = Forms!frmSwitch!TxtPassID;"

That's three double quotes in a row before and after: assigning two double
quotes to a string puts a single double quote into it.

You might also find it better to use the Execute method of the Database
object instead. The Execute method doesn't require that you use SetWarnings,
plus will generate a trappable error if something goes wrong. However,
you'll need to put the reference to the form control outside of the quotes:

Dim UPSQL As String

UPSQL = "UPDATE tblPass SET tblPass.Type = """ & Data & """ " & _
"WHERE tblPass.PassID = " & Forms!frmSwitch!TxtPassID
CurrentDb.Execeute UPSQL, dbFailOnError

That assumes that PassID is a numeric field. If it's text, you'll need

Dim UPSQL As String

UPSQL = "UPDATE tblPass SET tblPass.Type = """ & Data & """ " & _
"WHERE tblPass.PassID = """ & Forms!frmSwitch!TxtPassID & """"
CurrentDb.Execeute UPSQL, dbFailOnError

(that's four double quotes in a row at the end.)
 
If "Data" is text information and "Type" is a text field, then your SQL
should be:
UPSQL = "UPDATE tblPass SET tblPass.Type = '" & Data & "' " & _
"WHERE tblPass.PassID = Forms!frmSwitch!TxtPassID;"

Notice the addition of apostrophes inside the quoted strings.

BTW, as far as I can see, the two examples you gave are identical.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Because Data is a word, you need single quotes around it and
Forms!frmSwitch!TxtPassID needs to be outside the string (assuming that this
is a number)
UPSQL = "UPDATE tblPass SET tblPass.Type = '" & Data & "' WHERE
tblPass.PassID = " & Forms!frmSwitch!TxtPassID & ";"
 
Thanks Douglas, The 3 quotes did the trick. Thanks for the Execute Method,
this is the second mention of it, so I'm going to change my ways!
Thanks
DS
 
Your right Roger they are. I meant to add the '" to the second one. It's
funny this works if its a number....'" & 6 & "' but not '" & Data & "'
the field is a Text field.
Thanks
DS
 
Back
Top