Log changes using Insert Into

[BobJones]

Basically my boss wants it so that all changes made by staff to the database are logged. Logging the username, datachanged, form the data was changed on, and the date and time...
I made a public sub but it doesnt seem to be working, any ideas? I was thinking about putting it on the afterupdate of the controls, unless anyone has an easier, more efficient way of doing this?

Public Sub logData(ByVal uName As String, ByVal data As String, ByVal fName As String)
Dim strSQL As String
Dim day, time As String
day = Format(Date, "dd/mm/YYYY")
time = Format(Now, "hh:mm:ss")

strSQL = "INSERT INTO 'Log' ('Username', 'DataChanged', 'Date', 'Time', 'FormChangedOn')" & _
"VALUES ('" & uName & "', '" & data & "''" & day & "','" & time & "', '" & fName & "');"

DoCmd.RunSQL (strSQL)
End Sub

 

[ColinEssex]

Search for "Audit Trail" there are excellent examples posted


Col

 

[workmad3]

SQL should probably be

strSQL = "INSERT INTO Log (Username, DataChanged, [Date], [Time], FormChangedOn)" & _
"VALUES ('" & uName & "', '" & data & "', '" & day & "','" & time & "', '" & fName & "');"

as you only need to quote data, and you had a missing comma