Have you advanced beyond that? The SQL hasn't run yet in your image. Presuming so, use this to test the SQL in a new query and see if you get an error:
https://baldyweb.com/ImmediateWindow.htm
Accidentally duplicated post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Why are you saving user name instead of ID? Same could be asked about Description - do you have a set of standardized descriptions?
You have actually run the command and not just stopped as shown in image?
Description is a reserved word. Really should not use reserved words as names. Try enclosing Description in brackets: [Description]. http://allenbrowne.com/AppIssueBadWord.html
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Yes, I have run the whole code with no results or errors. I'm trying to capture changes of records to a separate table for tracking.
Does this work?
Cheers,Code:Public Function LogIt (sDescription as String,sNote as String, sUser as String With CurrentDb.OpenRecordset("tblLogLocal", , dbSeeChanges) .AddNew ' Add new record. !Description = sDescription !Note = sNote !User = sUser 'End If !UpdatedOn = Now() 'a (new) time stamp field .Update ' Save changes. .Close End With End Function
Thanks for the replies. I will have to follow up at a later time.
Doesn't matter, my point is you are still saving text data when you should probably be saving ID. User is who made the change - save their ID not their full name.
Review http://allenbrowne.com/AppAudit.html
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Sorry for such a delay in responding. Life is busy.
So, I decided to add a field, [User_ID], to the form so when I copy the record to an Audit Table it will contain who made the changes. It also is used to show who made the last change on the form.
No reserved words used.
Not text, but ID for reference in Audit table.
Again, thanks for everyone's input.
Final code:
Code:Private Sub Form_AfterUpdate()Dim S As String On Error Resume Next DoCmd.SetWarnings False S = "INSERT INTO tblProjectDetailAuditTrail SELECT * FROM tblProjectDetail " & "WHERE ID=" & ID DoCmd.RunSQL S DoCmd.SetWarnings True End Sub