Re: tracking changes...this is getting a little too confusing for me

"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.

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

.

Relevant Pages

  • Re: Multi User Log System
    ... transaction argument can ether be Before Update or Delete): ... Dim db As Database, rs As Recordset, strSQL As String, strUser As String ... If ctl.Name Like "cmd*" Then GoTo Skip ...
    (microsoft.public.access.forms)
  • Re: I need help -- Creating a history table for changes
    ... Dim db As Database, rs As Recordset, strSQL As String, strUser As String ... Set db = CurrentDb ... If ctl.Name Like "cmd*" Then GoTo Skip ...
    (microsoft.public.access.formscoding)
  • Re: CryptAPI
    ... > Private Declare Function CryptAcquireContext Lib "advapi32.dll" Alias ... > As String, ByVal dwProvType As Long, ByVal dwFlags As Long) As Long ... > On Error GoTo ErrSign ...
    (microsoft.public.vb.winapi)
  • Re: Error when running vb app with FlexGrid control
    ... Private Sub cmdNetChange_Click ... On Error GoTo Command1_Click_Error ... Private Sub fnGetData(strParam0 As String, strParam1 As String, FLX As ... Dim rsADOObject As Recordset ...
    (microsoft.public.vb.general.discussion)
  • Re: Command button problem
    ... Sorry about the goto, I know that's bad:) basically what I am trying ... Dim sServer As String, sUser As String ... Dim sNewPass As String, sOldPass As String ... sNewPass = StrConv(txtNew, vbUnicode) ...
    (microsoft.public.vb.general.discussion)