Syntax Error running a Visual Basic SQL query in MS Access

Question

I'm trying to create a log table in MS Access to track changes to the date field in a table. Every time I try to change a field value in the form, I get a syntax error message saying: "Syntax error (missing operator) in query expression." I've tried researching how to fix it but I've so far been unsuccessful. Here is my query:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog SELECT * FROM Date_table " & _
        "WHERE [Unique Key]=" & [Unique Key]
    End Sub
      

Is there anything I'm missing here? Thank you, new MS Access user here!

Answer 1

Without seeing the data types involved, I am guessing that [Unique Key] might be text. If that is the case, you need to put the actual value in single quotes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog SELECT * FROM Date_table " & _
        "WHERE [Unique Key]='" & [Unique Key] & "'"
    End Sub

As a side note, an INSERT statement without the column list and SELECT * are both poor practices. You should specify the column names in both. Since I don't know what your columns are, let's pretend they are ColA, ColB, and ColC

Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog(ColA, ColB, ColC) SELECT ColA, ColB, ColC FROM Date_table " & _
        "WHERE [Unique Key]='" & [Unique Key] & "'"
    End Sub