audit Log

[RuralGuy]

There is another thread on this topic on this Forum you may find interesting. Subscribe to the thread and we will post when it is solved.


https://www.accessforums.net/access/...form-8037.html

[thekruser]

Ok, thank you. I will begin implementation. I will post my findings.

[thekruser]

Maybe a dumb question, but an honest one: does the module need to be created in the FE, BE, or both?

[thekruser]

There is another thread on this topic on this Forum you may find interesting. Subscribe to the thread and we will post when it is solved.
https://www.accessforums.net/access/...form-8037.html

Subsrcibed. Thank you for your help!!

[RuralGuy]

Maybe a dumb question, but an honest one: does the module need to be created in the FE, BE, or both?

The *only* thing usually in the BackEnd of a split system are tables and relationships. The FrontEnd can not find code in a standard module easily if it is not in the FrontEnd.

[thekruser]

That is what I have always understood as being true, but this is my first go-around with something this adavnced. Thanks!!

[RuralGuy]

One of the biggest benefits of splitting the db, IMHO, is that *all* of the code and forms can be replaced at once without affecting the actual data in the tables simply by putting in a new FrontEnd.

[thekruser]

Ok...I implemented it; however, now I have a new question. Does the required code need to be placed in every subform or just the main form that contains the subforms?

I added it to all of the subforms, but am wondering if it would have been easier to just add to the main form. I just don't know if it would work. Any ideas??

[RuralGuy]

Ok...I implemented it; however, now I have a new question. Does the required code need to be placed in every subform or just the main form that contains the subforms?

I added it to all of the subforms, but am wondering if it would have been easier to just add to the main form. I just don't know if it would work. Any ideas??

It needs to be in *every* Form and SubForm where you wish to maintain an AuditTrail.

[jpkeller55]

What I did was paste the code into a Module, called module1. Then in each Form and subform Before Update property type =AuditTrail([Form])

[RuralGuy]

That should work if you are talking about the other thread I posted, but *not* for Allen Browne's method which is what is being discussed in this thread.

[thekruser]

Is there a way to get Allen Browne's methdod to work with a table that does not use a AutoNumber for a PK? My main table's PK is a txt field for Employee# that is assigned by HR. I cannot change this now. It would required having to redo the entire database.

[RuralGuy]

Probably, but I would need to look at Allen's code again. I just got back into town from a conference.

[RuralGuy]

Is there a way to get Allen Browne's methdod to work with a table that does not use a AutoNumber for a PK? My main table's PK is a txt field for Employee# that is assigned by HR. I cannot change this now. It would required having to redo the entire database.

You could get it to work but you would need to alter all of the routines he created which expect a LongInteger as the unique identifier of the record.

[pkstormy]

Even though I use SQL Server, I will still create a 'changelog' table in MSAccess to record critical changes (since it's much quicker to simply look at a changelog type of table versus tracking it down in the SQL Server logs).

I'll have a table called: tblCustomersChangeLog which has these key fields:
RecID (autonumber)
FieldNameChanged (text)
OldFieldValue (text)
NewFieldValue (text)
ChangedBy (text - using the GetUser routine found in the code repository)
DateTimeChanged (date/time)

I'll then write a simple function which opens this table and writes the appropriate values. For example, when a critical field gets the focus (ie. OnFocus), I'll store the value of that field (if it's not null) into a variable. Then in the AfterUpdate event of that field, I'll check to see if my variable is different than the newly entered data and if so, I'll call my function to write to the tblCustomersChangeLog table.

Example of the Function (in a module)
Function writeCustomerChangeLog(FldName as variant, OValue as variant, NValue as variant)
dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "Select * from tblCustomerChangeLog"
rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
rs.addnew
rs!FieldNameChanged = FldName
rs!OldFieldValue = OValue
rs!NewFieldValue = NValue
rs!ChangedBy = GetUser()
rs!DateTimeChanged = Now()
rs.update
rs.close
set rs = nothing
End Function

and then for the 'critical' field's OnFocus event (say it's the FirstName field)...
if not isnull(me!FirstName) then
MyVariableName = me!FirstName
end if

(note: I'll "Dim MyVariableName as variant" for the form under the Option Compare Database or Option Explicit so that any events on the form recognize the MyVariableName and I don't have to Dim it for each OnFocus event.)

and then for the 'critical' field's AfterUpdate event...
if me!FirstName <> MyVariableName then
Call writeCustomerChangeLog("FirsName", MyVariableName, me!FirstName)
end if

Sometimes I will also name the table just 'changelog' and add a field called: TblName (text) to the table and tweak the function to pass the table name (and make it so strSQL = "Select * from " & TblName).

Again though, I'll do this on the critical fields since it's often unnecessary to record changes on 'every' field that's changed.

Another thing I typically do is have 4 key fields in my main table (ie. tblCustomers). These fields are:
DateEntered (datetime)
EnteredBy (text)
DateModified (datetime)
ModifiedBy (text)

and then on the form, I'll have DateEntered as a defaultvalue of =Now(), EnteredBy as a defaultvalue of =GetUser() (again, see GetUser in code repository). Then in the Form's BeforeUpdate event, I'll write to DateModified and ModifiedBy fields. The DateEntered and EnteredBy tells me who entered the record and when. The DateModified and ModifiedBy tells me who "last" modified the record and when (but this only tells me who/when the record was modified and doesn't tell me specifically what data was modified.) Often times this is sufficient for then going to the user and asking what they might have modified for that record or show trends on users who are radically modifying records in the db.