"sparks" <sp****@comcast.netwrote in message
news:ub********************************@4ax.com...
At first they just wanted to keep a record of who logged in and when.
Then it was if they made changes.
Now its who changed what. BUT since this made no since to them.
WHO put it in and who changed it.
WTF are they talking about.
So every variable will have to be logged at entry and later IF someone
changes it it will have to be logged like from and to ...then by who
and when ???
I have no way of figuring this out. CAN it be done???
has anyone ever tried this before ?
Tracking changes is fairly simple in principle but it requires a fair bit of
coding. I've attached the code that I use in one of my apps which needs to
be called from the form's Update and Delete events and needs certain tables
and queries to be in place, but you should be able to gleen their names and
characteristics from the code. I also pass values from certain controls on
the form and the actual form object itself. Call the function using
Call libHistory(Me, Me.txtIssueNo, "BeforeUpdate")
"Me" is the form object, "Me.txtIssueNo" uniquely identifies the record and
"BeforeUpdate" is the calling event.
This is fairly old code and could probably be tidied up and made more
elegant but it does work. Hope it helps.
Keith.
www.keithwilby.com
Public Function libHistory(frmForm As Form, lngID As Long, strTrans As
String)
'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events
Dim ctl As Control
Dim db As DAO.Database, rs As Recordset, strSQL As String, strUser As
String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)
strUser = fOSUserName()
For Each ctl In frmForm
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
If ctl.Name Like "btn*" Then GoTo Skip
If ctl.Name Like "txtXPID" Then GoTo Skip
If ctl.Name Like "*Master*" And frmForm.Name Like "sfrm*" Then GoTo
Skip 'Don't record the MasterID as a seperate transaction
If ctl.SpecialEffect = 0 Then GoTo Skip
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If (IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <ctl.Value Then
With rs
.AddNew
![DataSource] = frmForm.Name
![ID] = lngID
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue 'Don't record Old Value for
an appended comment
If strTrans = "Delete" Then 'Record the fact that the
record was deleted
![NewValue] = strTrans
Else
![NewValue] = ctl.Value
End If
![UpdatedBy] = strUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
