Syntax Help

[businesshippieRH]

I'm trying to figure out the proper syntax for this SQL statement, but have hit a wall (and: No. More coffee did not help). Any help would be great! Thanks!

Private Sub btn_Cancel_Click()
'Turn warnings off
    DoCmd.SetWarnings (WarningsOff)
'Run Append Query for Canceling Document
    'Set Strings so that SQL works properly
    Dim CancelNotes As String
    CancelNotes = "This record was canceled prior to completing information entry."
    Dim TodayDate As String
    TodayDate = Format(Date, "MM/DD/YYYY")
        DoCmd.RunSQL "INSERT INTO tbl_Records (CancelDate, CancelNotes)" & _
            "SELECT " & TodayDate & "AS Expr1, " & CancelNotes & "AS Expr2" & _
            "WHERE [Tables]![tbl_Records]![RecordName] = [Forms]![frm_NEWRecord]![txt_RecordName];"
'Turn warnings back on
    DoCmd.SetWarnings (WarningsOn)
'Close the Form
    DoCmd.Close acForm, "frm_NEWRecord"
End Sub

 

[Mihail]

While I'm not comfortable with SQL I developed other technique in order to solve situations like this:
I create an working query that do what is supposed to do.
Then I copy the SQL and paste it in VBA.
Then I tweak this in order to insert variables as criteria.

For sure the code is less efficient than the SQL produced by a skilled guy, but works.

 

[TJPoorman]

I'm pretty sure that you're looking for an UPDATE statement, not INSERT. Would this work?

Private Sub btn_Cancel_Click()
CurrentDb.Execute ("UPDATE tbl_Records SET CancelDate=Date(), " & _
    CancelNotes='This record was canceled prior to completing information entry.' " & _
    WHERE RecordName=" & Me.txt_RecordName)  [COLOR="SeaGreen"]'NOTE: You will need to change this to [COLOR="Red"]RecordName='" & Me.txt_RecordName & "'"[/COLOR] if your RecordName field is text.[/COLOR]
'Close the Form
    DoCmd.Close acForm, "frm_NEWRecord"
End Sub

PS. You'll notice that using CurrentDb.Execute will avoid having to turn warnings off and on

 

[businesshippieRH]

Thanks TJ Poorman! You were right. I did want an update. I wound up with:

Private Sub btn_Cancel_Click()
'Turn warnings off
    DoCmd.SetWarnings (WarningsOff)
'Run Append Query for Canceling Document
    'Set Strings so that SQL works properly
    Dim CancelNotes As String
    CancelNote = "This record was canceled prior to completing information entry."
    Dim TodayDate As String
    TodayDate = Format(Date, "MM/DD/YYYY")
        DoCmd.RunSQL "UPDATE tbl_Records" & _
        " SET CancelDate = (#" & TodayDate & "#), CancelNotes = '" & CancelNote & "'" & _
        " WHERE RecordName = ([Forms]![frm_NEWRecord]![txt_RecordName]);"
'Turn warnings back on
    DoCmd.SetWarnings (WarningsOn)
'Close the Form
    DoCmd.Close acForm, "frm_NEWRecord"
End Sub

I'll definitely look into the CurrentDb method, but for right now, I would prefer calling every little detail in code somewhere. (I know somebody else will eventually be trying to use/manage this database after I get it put together).