Re: tracking changes...this is getting a little too confusing for me
- From: "Keith Wilby" <here@xxxxxxxxx>
- Date: Tue, 22 Apr 2008 16:00:41 +0100
"sparks" <sparks@xxxxxxxxxxx> wrote in message news:ubrr0453r8botj0df8s43r9rrrdl51h8t6@xxxxxxxxxx
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.
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
![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
![NewValue] = ctl.Value
![UpdatedBy] = strUser
![UpdatedWhen] = Now()
Set rs = Nothing
Set db = Nothing
- Prev by Date: Re: Are Macros Evil?
- Next by Date: Report formatting Disappears on Some Items when Output to PDF in Code
- Previous by thread: Re: tracking changes...this is getting a little too confusing for me
- Next by thread: Help with Google Maps