Unanswered: How to keep track of modified records?
I am trying to automate keeping track of any modification to records in an Access DB.
Using OnChange event for individual control on a specific form works fine. The code looks something like this:
Dim msg, reply as String
msg = "You are changing an existing record. Please explay why."
Me!Note =Me!Note & InputBox(msg, "Changing record")
Me!DateModified = Now()
' Note is the memo field
' DateModified is a date field
If a record contains several controls, then I guess I have to add this code to all of them.
In addition to this I have several forms for inputing different type of information.
In order to centralize this I want to write a sub to handle all changes and to send all notes to only one memo box for each client. Example: there are records about students, registrations, marks, etc. If the user changes any information, regardless on which form, then I want to put these notes in the memo box for this client.
I ran into a problem when trying to identify the name of the form where the control is.
Me.form.name - doesn't not work --> inproper use of Me.
Here is some code to look at:
Option Compare Database
Public Sub ChangeRecordBox()
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Students")
Dim msg, reply, boxname As String
'--- boxname should identify what change is made - registration, marks, etc.
'--- BoxName should change automaticaly depending on the name of the form where the code is used
Dim FormName As String
FormName = Me!Form.Name
boxname = FormName
msg = "You are about to change a record!" & vbCrLf & "Please enter the reasons for this below."
reply = InputBox(msg, boxname)
'----after getting the input, find the record in question in the recordset and add the answer from the Inputbox to the memo box
It sounds as though you are trying to implement some type of audit trail mechanism. I don't know how dynamic your data is.. but you may want to consider approaching implementing this in another way. Memo fields are notoriously slow, contain up to 64,000 bytes (which sounds like alot but depending on how dynamic your data is and how verbose your users may be.. could fill up really fast!) and they can't be sorted or searched, so researching them can be very laborious. Your idea of a DateModified column is a good one.. have you considered determining if the the most common reasons for changes to various types of records can be put into a drop down.. that way they can be standardized and added to a table along with your date modified and record ID that would serve as your audit trail. You could also add a "other' reason for the change which would require the user to type in an explantion if it is not one of the common ones, and that could be added to the audit trail table. A source of information .... and other ideas for implementing this is at http://users.bigpond.net.a-
Thanks for your help. I did visit your web page and reviewed your tip how to do the audit log. It is probably an excellent solution for some cases. In my case I have three forms storing data in three different tables therefore I may need to modify the code for each table. I hope to find a simpler solution.
To answer your question - my DB would not be very dynamic. The changes to existing records would be mostly to fix erroneus input, which may happen a few times, but probably not more than 10 times for each client. The data collected into the memo field would be needed only if an explanation is required why something has been changed, by who, and when.
Since the code is called from a control on some of the forms, how do I find the name of the form and reference it in the code.
1. Track changes only of the field "Discount" on form "Registrations". The value of "Discount" is stored in "tblRegistrations".
2. Track changes of "Mark" on form "EnterMarks", stored in "tblMarks".
The questions is: If I call the code from any ot the above controles ("Discount" or "Mark"), how do I automatically reference the appropriate form in the code?
Any comments are welcome.
Thanks for the help again,