I have an MS Access database with multiple tables, and each table has different fields (each table has a primary key). Whenever someone changes an existing record, I want the before/after status to be logged into a 'history' table, but I'm trying to avoid making a DataMacro for 20+ fields per table, since that seems cumbersome and difficult to manage.
To accomplish this, I've added "Before Change" and "After Update" macros that each have one call of SetLocalVar to run public vba functions that reside in a local module. The "before" function cycles through whatever fields exist in the current table to build a dictionary of pre-change values. The "after" function repeats the process to identify the field that changed, so that information can be added to a history table (contains table name, field name, before/after values, user, time stamp, etc.).
The problem I'm having is that the "After Update" function only reveals the pre-change data. I can't tell what the new value is or which field changed. I can't feed [Old].[FieldName],[FieldName] as inputs to SetLocalVar because I don't know beforehand which fields will be updated (and field names differ between tables). And I can't call Requery or Refresh because it collides with the "After Update" process.
Of all the similar questions asked here and elsewhere that I can find, they either start with explicitly knowing which field will change, or else not providing that level of detail in their log events. Or maybe my google-fu is weak.
Here is what I have:
Before SetLocalVar expression (different input table name per table):
=SetupLogEvent("Table01",[KeyName])
After SetLocalVar expression:
=SubmitLogEvent("Table01",[KeyName])
Module Code
Public BeforeFields As Scripting.Dictionary
Public Function SetupLogEvent(ByVal TableName As String, ByVal KeyName As String)
Set BeforeFields = New Scripting.Dictionary
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE KeyName='" & KeyName & "'")
Dim i As Long
With rs
.MoveLast
.MoveFirst
For i = 0 To rs.Fields.Count - 1
fName = rs.Fields(i).Name
fVal = rs.Fields(i).Value
BeforeFields.Add fName, fVal
Next i
End With
rs.Close
SetupLogEvent = True
End Function
Public Function SubmitLogEvent(ByVal TableName As String, ByVal KeyName As String)
DoEvents
Dim MakeUpdate As Boolean
MakeUpdate = False
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE KeyName='" & KeyName & "'")
With rs
.MoveLast
.MoveFirst
For i = 0 To rs.Fields.Count - 1
fName = rs.Fields(i).Name
fVal = rs.Fields(i).Value
If fVal <> BeforeFields(fName) Then
Debug.Print ("Modified field is " & fName)
MakeUpdate = True
Exit For
End If
Next i
End With
rs.Close
If MakeUpdate = True Then
'Run SQL code to update the history table with the relevant information
'DoCmd.RunSQL <SQL code here>
End If
Set BeforeFields = Nothing
SubmitLogEvent = True
End Function
I suspect I should be using some method other than cycling through the recordset, but I'm not sure what I should be doing instead. I suppose I could add a couple dozen optional inputs to the after function and manually feed in each [FieldName] of each table into each table's SetLocalVar expression, but I can't imagine the system being so inflexible as to require that. There's got to be a better way, right?
Edit: I've updated the title of this question and my reason for avoiding writing a DataMacro for each field for future readers of this topic.