Update Query doesn't Update? (1 Viewer)

[Valentine]

I thought that creating an update SQL that it would add to the existing data in the table but it instead overrides the data with the new data.

Private Sub cmdComment_Click()

    Dim strComment As String

    strComment = "UPDATE Leave " _
               & "SET [Comments] = '" & Me.txtComment.Value & "' " _
               & "WHERE ((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strComment)
    DoCmd.SetWarnings True
    
    Me.txtComment = ""

End Sub

I want the ability to add to the end of what is allready there

 

[Gasman]

You need to update with Comments with [Comments] & Me.txtComment
Think about it, you want to add to the end of the existing comment, which the above would do. You are setting the comment to the form comment, so it is only doing what you have told it to do?

 

[Valentine]

ah ok thank you I wasn't thinking i guess.

 

[Valentine]

Is there a way to add in a separator between [comments] and me.txtComment? I want a "," or ";" but im getting syntax and if I add in a single quote then it comments out the line.

Private Sub cmdComment_Click()

    Dim strComment As String

    strComment = "UPDATE Leave " _
               & "SET [Comments] = [Comments] & "; " & " " '" & Me.txtComment.Value & "' " _
               & "WHERE ((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strComment)
    DoCmd.SetWarnings True
    
    Me.txtComment = ""

End Sub

 

[Gasman]

You concatenate each string in turn?

"SET [Comments] = [Comments] & "; " & "'Me.txtComment.Value & "'" _

TBH not sure the single quotes are required in this scenario?

tt="one word"
tt2= "two word"
? tt & "; " & tt2
one word; two word

 

[Valentine]

everything i try gives me a syntax error end of statement highlighting the semicolon

 

[The_Doc_Man]

    strComment = "UPDATE Leave " _
               & "SET [Comments] = [Comments] & "; " & " " '" & Me.txtComment.Value & "' " _
               & "WHERE ((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"

You have improper quoting where you show SET [Comments] = [Comments] & "; " & ... because the ampersand should go OUTSIDE of the quoted string for proper concatenation. I think you are actually MISSING a quote before the first ampersand, and thereafter your quoting is completely out of sync. Perhaps SHOULD be ... = [Comments] " & ";" to keep proper balance. You get a highlight on the semicolon because at that point it is "exposed" rather than encapsulated by quotes (due to the quote imbalance.)

 

[cheekybuddha]

& "SET [Comments] = [Comments] & "; '" & Me.txtComment.Value & "' " _

 

[Valentine]

Private Sub cmdComment_Click()

    Dim strComment As String

    strComment = "UPDATE Leave " _
               & "SET [Comments] = [Comments]" & "; '" & Me.txtComment.Value & "' " _
               & "WHERE ((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strComment)
    DoCmd.SetWarnings True
    
    Me.txtComment = ""

End Sub

now it is thinking the SQL statement ends at the first semicolon and i get an error saying "Characters found after end of SQL statement"

 

[cheekybuddha]

Oops!

Should have been:

& "SET [Comments] = [Comments] & "'; " & Me.txtComment.Value & "' " _

 

[Valentine]

Oops!

Should have been:

& "SET [Comments] = [Comments] & "'; " & Me.txtComment.Value & "' " _

that just comments out the line

 

[cheekybuddha]

Not on form today! That's the problem with answering from my phone!

Ok, try:

& "SET [Comments] = [Comments] & '; " & Me.txtComment.Value & "' " _

 

[Valentine]

Awesome that time it worked thank you

 

[cheekybuddha]

Third time lucky!

All that said, you might consider having a separate table for comments with a one to many relationship. You can also store time of comment and by whom it was made, and the user can just add it directly rather than via SQL

 

[Pat Hartman]

Bottom line. This is a poor idea. Just add a new row to the comments table. This allows you to properly track who made the comment and when. It also gives you the ability to control updates. Most of the time, I don't want to allow updates to clinical notes but to avoid confusion, the users have decided that the original nurse has 24 hours to update the notes (and we automatically record this event). That way we have history of what happened if we need it but the clinical notes are clean.