Unanswered: How can I know the change after each update?
I have the following query.
I am going to use MS ACCESS to store the do-list. If some procedure is changed, I need to update the do-list. After update, can I find the change history in MS ACCESS? It is because I need to verify or double check the update.
I don't know if this would help you, but this is how I tracked my changes. I'm new to VBA so please don't laugh at me.
First create a table almost identical to the one you're gathering info from. i.e. To Do Item, Date Due, Date Completed, etc. Then change them and add a second field for each so you end up with a table like this:
Old To Do Item, New To Do Item, Old Due Date, New Due Date, etc. I hope I haven't confused you so far. Then, make a form which will show what it is you need to show.
Open that form in design view and then open the VBA view. Go to the field you want to update, say... To Do Item. Go to the AFTERUPDATE type this:
Dim db as database
Dim rs as recordset
Set db = currentdb()
Set rs = db.openrecordset("Select*from[name of new table]")
rs![Old To Do Item] = To Do Item.OLDVALUE
rs![New To Do Item] = To Do Item.VALUE
rs![Date] = Now()
I think this can help me but I would like to brief the case once.
Currently, I record the do-list item in EXCEL format. If there is a change on the do-list item, I would make a copy of the do-list and edit on the copy. After editing, I would compare two EXCEL files. Then I can know the change after comparison and I can make sure everything is correct.
Now, I would like to use Access for the do-list. If there is a change on the do-list item, can I know the change after editing? Can I use compare for ACCESS? Is there any history log?
There's no audit log that I'm aware of, I've always rolled my own when required. It's not something for the newbie (don't take it the wrong way) but you have to place code in every forms "before update", "after update", "before add", "before delete", "after delete" events and then update the audit log manually on each iteration. Its nasty, messy and not for the faint hearted.
yiou need to keep track of what the action is (ie add, change or delete) in some circumstances you need to identify what the data was before an action occurred (esp delete), you need to handle the difference between an addition and a record added using a paste - as I said its messy and not reccomended for the beginner. It stills scares me and I've been dicking around with VB / VBA for far too many years than I'd care to admit.