Update unable to accept appostraphes or Commas (1 Viewer)

[violentjay25]

This update is unable to use apostraphes and commas when updating. My insert into statement works just fine. If I do not use apostraphes or commas the update works. What am I doing wrong

DoCmd.RunSQL "UPDATE tbl_Projects " & _
"SET ProjectName = '" & Me.cboProjectName & "', " & _
" SystemsImpacted = '" & Me.TxtSystemsImpacted & "', " & _
" SPRNum = '" & Me.txtSPR & "', " & _
" ReleaseDate = '" & Me.txtReleaseDate & "', " & _
" Status = '" & Me.CboStatus & "', " & _
" CSIPM = '" & Me.txtCSI & "', " & _
" BPM = '" & Me.txtBPM & "', " & _
" Implemented = '" & Me.cboImplemented & "', " & _
" StakeHolder = '" & Me.cboStakeholder & "', " & _
" IBR1 = '" & Me.cboIBR1 & "', " & _
" IBR2 = '" & Me.cboIBR2 & "', " & _
" IBR3 = '" & Me.cboIBR3 & "', " & _
" Objective = '" & Me.txtObjective & "', " & _
" SMERequirments = '" & Me.txtSMERequirements & "', " & _
" Phase = '" & Me.cboPhase & "' " & _
"Where ProjectName = '" & Me.cboProjectName & "'"

DoCmd.RunSQL "Insert into tbl_Projects (ProjectName, SystemsImpacted, SPRNum,ReleaseDate, Status, CSIPM, BPM, Implemented, StakeHolder, IBR1, IBR2, IBR3, Objective, SMERequirments, Phase) " & _
"Values ('" & Me.txtProjectName & "', """ & _
Me.TxtSystemsImpacted & """, """ & _
Me.txtSPR & """, """ & _
Me.txtReleaseDate & """, """ & _
Me.CboStatus & """, """ & _
Me.txtCSI & """, """ & _
Me.txtBPM & """, """ & _
Me.cboImplemented & """, """ & _
Me.cboStakeholder & """, """ & _
Me.cboIBR1 & """, """ & _
Me.cboIBR2 & """, """ & _
Me.cboIBR3 & """, """ & _
Me.txtObjective & """, """ & _
Me.txtSMERequirements & """, '" & _
Me.cboPhase & "')"

 

[RoyVidar]

You need the double quoting technique applied to the update statement too. I e replace the single quote/double quotes with couple more double quotes, as in the insert statement.

 

[violentjay25]

That almost worked

I changed my code but now it tells me it will not append the row due to Key violations. Here is my code now

DoCmd.RunSQL "UPDATE tbl_Projects " & _
"SET ProjectName = "" & Me.cboProjectName & "", " & _
" SystemsImpacted = "" & Me.TxtSystemsImpacted & "", " & _
" SPRNum = "" & Me.txtSPR & "", " & _
" ReleaseDate = "" & Me.txtReleaseDate & "", " & _
" Status = "" & Me.CboStatus & "", " & _
" CSIPM = "" & Me.txtCSI & "", " & _
" BPM = "" & Me.txtBPM & "", " & _
" Implemented = "" & Me.cboImplemented & "", " & _
" StakeHolder = "" & Me.cboStakeholder & "", " & _
" IBR1 = "" ' & Me.cboIBR1 & "", " & _
" IBR2 = "" & Me.cboIBR2 & "", " & _
" IBR3 = "" & Me.cboIBR3 & "", " & _
" Objective = "" & Me.txtObjective & "", " & _
" SMERequirments = "" & Me.txtSMERequirements & "", " & _
" Phase = "" & Me.cboPhase & "" " & _
"Where ProjectName = '" & Me.cboProjectName & "'"

 

[violentjay25]

Its the last 2 statements

I know its the last 2 statments that may be the issue

 

[RoyVidar]

It seems you are trying to update also on the column you are using a criterion, if that's the primary key, don't include it in the update statement.

 

[violentjay25]

No Dice

This did not work. The record will still not update

 

[RoyVidar]

Then take a closer look at the differences between the insert and the update statements, with regards to the double quoting (hint - number of quotes)

 

[violentjay25]

Do you know whats wrong?

IF you know exactly whats wrong could you please just tell me. I suck at coding and need to get this done by the End of the Day. I would really appreciate it

 

[RoyVidar]

From initial post, insert

"Values ('" & Me.txtProjectName & "', [b]"""[/b] & _
Me.TxtSystemsImpacted & [b]""", """[/b] & _
Me.txtSPR & """, """ & _...

from last post, update - note the number of quotes on text fields (2), vs the insert (3)

"UPDATE tbl_Projects " & _
"SET SystemsImpacted = [b]""[/b] & Me.TxtSystemsImpacted & [b]""[/b], " & _
" SPRNum = [b]""[/b] & Me.txtSPR & [b]""[/b], " & _...

it's probably supposed to look something like this

"UPDATE tbl_Projects " & _
"SET SystemsImpacted = [b]"""[/b] & Me.TxtSystemsImpacted & [b]"""[/b], " & _
" SPRNum = [b]"""[/b] & Me.txtSPR & [b]"""[/b], " & _ ...

and no, I don't know exactly what's wrong, but I suppose it'll work with the correct number of quotes on text fields. What I usually do, is reduce the complexity of the tasks, make a simple thingie work, then add the rest incrementally. I have more questions on the structure, you seem to use dates, but there are no # surrounding them, but the insert works - so, I'm assuming either you're working with SQL server as backend, which is a useful information to have when assisting, or you are using text fields in access to store dates.

 

[violentjay25]

Oh God

Well yes I suppose that would be helpful to let you know Im using a SQL backend. My brain is fried at this point. Sorry for the confusion andI appreciate all the help you have give me

 

[violentjay25]

I got it to work

Thank you for your help