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
