Track Changes in DB

I'm looking for suggestions as to the best way to implement a "record change event log." I'd like to store the value of a particular field or fields if it is changed (by means of a Form). Please see table structure below.

tbl_Membership:
pk: rec_ID
fname
lname
addr1
addr2
etc...

tbl_Mbr_Changes:
fk: rec_ID
changed_field
changed_value
timestamp
user

I'm assuming I would need to use the OnCurrent event to append the current record to a temporary table (to store initial values) and go from there. I'm confused on what to do next. Should I have an OnChange event for each field? Seems like there would have to be a better way. Any help appreciated.

Wyn Bryant

Are you using Bound or UnBound Forms and Text boxes?

S-

S-

This particular form is bounded. I was able to figure out how to store the value before change, but my code is very inefficient. I have several AfterUpdate() events (one for each field) that all run basically the same SQL append with the only change being the field name. How would I structure a function call with the variable being the field name?

Here are my current AfterUpdate() events:




How would I reference the FIELDNAME variable?

Here is two suggestions:

1. (My personnal perference) You seem familar with unbound forms, so don't use bounded ones. Use a unbounded form that everytime you hit a update button, it creates your adds the records to you history table as part of the action of editing the record

2. In the after_update event of each field change a "FieldchangeVariable" from False to true (different one for each field) that will state a change has taken place with this field. then don't use the form controls for moving from record to record, create buttons that do this so when the move to another record you look to see which field have changed (based on the FieldchangeVariable) and pass through you SQL where each variable is set to true. WHen done set all variables to false for next record. This will only record the last change to that field for each record.


As far as "How would I structure a function call with the variable being the field name"

VariableOne = "FieldName"

DoCmd.RunSQL "INSERT INTO t_Membership_Changes ( rec_id, field, changedval, changed_by, [timestamp] )

SELECT rec_id, " & VariableOne & " AS Expr1,

t_Membership_CurRec.[FIELDNAME] AS Expr2, fOSUserName AS Expr3, Now() AS Expr4;"


S-