Link to home
Start Free TrialLog in
Avatar of J G
J G

asked on

Access Report that will show table changes

Could you show me how this could be accomplished.  I will integrate it into my db.

I have a form that users will maintain table data on.  

I want there to be a report that will list/print all table records that were recently modified or added.  The idea would be for users to then take this report and use it to data enter or import into another database.

Ideally,  the report would list the entire record (all fields) , but would be sorted by the fields that were changed, as well as highlight the fields in bold.  Also it would sort new records that were added.  

I.E. sample field names - PLU, description, Size, Price etc) (| = field delimiter)

Report would show:

PLU changes:
None

Description changes:
94013| Apples OG |1Ea| 4.25

Size changes:
None

Price Changes:
94225| Avocadoes |1ea| 4.25

New Items:
94444 |Grapes Perlb| 5.99
Avatar of bfuchs
bfuchs
Flag of United States of America image

I use following function to keep history of any table, call it in before update event of form.
Public Function SaveHistoryChanges(Optional sTable As String = "", Optional sField As String = "", Optional vValue As Variant = "", Optional lID As Long = 0, Optional f As Form)
    Dim sSql As String, cNtrl As Control
    If Len(sTable) = 0 And f Is Nothing Then
        sTable = Screen.ActiveForm.Name
    ElseIf Not f Is Nothing Then
        sTable = f.Name
    End If
    If lID = 0 Then lID = Nz(Screen.ActiveForm.ID, 0)
    
    If f Is Nothing Then
        Set f = Screen.ActiveForm
    End If
    
    On Error Resume Next
    For Each cNtrl In f.Controls
        If (TypeOf cNtrl Is TextBox Or TypeOf cNtrl Is ComboBox Or TypeOf cNtrl Is CheckBox Or cNtrl.ControlType = 107) Then
            If Nz(cNtrl.Value) <> Nz(cNtrl.OldValue) And cNtrl.Visible = True And cNtrl.Enabled = True And Nz(cNtrl.Tag, "") <> "NoHist" Then
                If Not IsNull(cNtrl.OldValue) And Not (TypeOf cNtrl Is CheckBox) Then
                    If Nz(tLookup("ID", "HistoryChanges", "TableName = '" & sTable & "' and FieldName = '" & cNtrl.ControlSource & "' and TableID = " & lID), 0) = 0 Then
                        sSql = "Insert into HistoryChanges (TableName, FieldName, Value, TableID)"
                        sSql = sSql & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.OldValue & "'," & lID & ")"
                        CurrentProject.Connection.Execute sSql
                    End If
                End If
                sSql = "Insert into HistoryChanges (TableName, FieldName, Value, Initial, TableID)"
                sSql = sSql & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.Value & "','" & GetInitial & "'," & lID & ")"
                CurrentProject.Connection.Execute sSql
                
            End If
        End If
    Next

End Function

Open in new window

SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just to add a bit, logging changes is a little more involved then just tracking changes in fields.   You can read the following, which also has a complete solution for logging (and there are several out there like this):

http://allenbrowne.com/appaudit.html

But as you'll notice, Allen starts right off saying the new data macros are the best way to log, which is what Joe suggested.

Jim.
@tike55, did you tried my suggestion?
Avatar of J G
J G

ASKER

I did not because of the issues with the .OldValue property that Pat outlined.  

thanks for your help though.