VBA to Append table not working

[mainerain]

Everything seems to be set up right but no changes to table and no errors popping up.

[pbaldy]

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

[June7]

Accidentally duplicated post.

[June7]

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

[mainerain]

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.

[mainerain]

Here is the result.

[Gicu]

Does this work?

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

Cheers,

[pbaldy]

Here is the result.

Did you try running that SQL in a new query?

[mainerain]

Thanks for the replies. I will have to follow up at a later time.

[June7]

I'm trying to capture changes of records to a separate table for tracking.

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

[mainerain]

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:

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