Reputation: 15
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!
Upvotes: 0
Views: 135
Reputation: 1073
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
Upvotes: 1