I have a bound MS Access form which is updated by multiple users. I want to keep track of any edits by the users. Currently i record whenever a record is updated by using the timestamp and username.environ function However i would like to track the changed contents as well with an inactive flag.
For example my record is 101 | Software | Jan-2017 | $5600 | Expense for Software (desc) | Active
If someone was to make any change to it, i would like the changed record to be considered active but keep this record as inactive.
Any suggestions appreciated
The traditional way to do this sort of thing is with some sort of a flag on the record to indicate the current record (be it a boolean or a sequence number or a date stamp).
Then when you are working with the current data and you end up working on a selection from the table, pruning it down to just what is current (i.e., an sql statement with where archive_date is null or where active = 'T' or you could do it one the max value for the sequence/date stamp).
That style presents certain problems for bound forms. So your mileage may vary.
My quick and dirty way out of this would be to make a data history table (parallel structure to the one your working with, with an extra date/time field and a userid field), and then add a trigger (ideally at the database level, but you could do it on the controlling form) to log the new value in the history table.