Audit Log

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am trying to create an audit report of only changed fields and i've looked
at the code provided here http://support.microsoft.com/default.aspx/kb/197592
but instead of returning just the changes i am getting all the fields on the
form those whose values have changed and those that didn't change. can
someone please help me to grab only those values that have been edited? i
have spent all day on this and i could really use some help. thanks
 
Hi Raj
I created an audit some time ago. First I created two audit files. One is
for changed memo fields and the other for everything else. If you need to
cater for memo fields, you will need a memo field to store the before and
after. This means a very large file very quickly. I would consider two
files.

What you need to do is loop through the controls on the form and find which
one is changed. You can then write some information to the history file..
This is a cut and paste from my code so there are some other functions called
but you can probably get the drift. I pass a pile of information to the
function and you may want to handle it differently.

Post a message if you want more info.

Public Function funLogTrans(frm As Form, _
intKey As Integer, _
strFormName As String, _
strKeyName As String, _
Optional strOptional As String) _
As Boolean
' Frm is the form passing the information
' intKey is the value of the PK
' strFormName is the name of the form being
modified including full path for subforms
' strKeyName is the name of the Primary Key
field in the table e.g. "tblPeople.PersonNo"
' strOptional1 is the cancatenated descriptive
string.
Dim dbs As DAO.Database
Dim ctlCtrl As Control
Dim MyMsg As String
Dim strHist As String
Dim lngOldValue As Long
Dim lngNewValue As Long

' Loop through controls to find ones that changed
For Each ctlCtrl In frm.Controls
If (funActiveCtrl(ctlCtrl)) Then ' Check
it is an updateable control
If IsNoOldValue(ctlCtrl) = True Then ' Is
the oldvalue valid for this control
If ctlCtrl.Enabled = True Then ' Is
the control enabled.
If ((ctlCtrl.Value <> ctlCtrl.OldValue) _
Or (IsNull(ctlCtrl) And Not IsNull(ctlCtrl.OldValue)) _
Or (Not IsNull(ctlCtrl) And IsNull(ctlCtrl.OldValue)))
Then
lngNewValue = Len(IIf(IsNull(ctlCtrl), 0, ctlCtrl))
lngOldValue = Len(IIf(IsNull(ctlCtrl.OldValue), 0,
ctlCtrl.OldValue))
If lngOldValue > 255 Or lngNewValue > 255 Then
' If a memo, write to that table
strHist = "tblHistMemo"
' Memo table
Else
strHist = "tblHist"
' Non memo table
End If

' This function creates new history records
Call funAddHist(strHist, _
intKey, _
strFormName, _
strKeyName, _
ctlCtrl, _
strOptional)

' strHist = Select which table to
enter data into
' MyKey is the value of the PK
' strFormName is the name of the
form being modified including full path for subforms
' strKeyName is the name of the
Primary Key field in the table e.g. "tblPeople.PersonNo"
' ctlCtrl is the control that changed
' strOptional1 is the cancatenated
descriptive string
End If
End If
End If
End If
Next ctlCtrl

End Function
 
Thanks Neville, i will take a look and let you know. i have been trying to do
this for sometime now. i have two tables that holds the before update record
and one that holds the after update record. now i need to loop through and
fields and find the two that do not match. actually now that i'm typing this
it seems to me i've taken the long way to get the audit log done. i will let
you know if i am sucessful. thanks again
 
raja07 said:
i am trying to create an audit report of only changed fields and i've looked
at the code provided here http://support.microsoft.com/default.aspx/kb/197592
but instead of returning just the changes i am getting all the fields on the
form those whose values have changed and those that didn't change. can
someone please help me to grab only those values that have been edited? i
have spent all day on this and i could really use some help. thanks
Click to expand...

As alternatives see the following

Audit Trail - Log changes at the record level at:
http://allenbrowne.com/AppAudit.html
The article addresses edits, inserts, and deletes for a form and
subform.

Modules: Maintain a history of changes
http://www.mvps.org/access/modules/mdl0021.htm
The History Table routine is designed to write history records that
track the changes made to fields in one or more tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top