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

[sparks]

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 ?

[sparks]

Just wanted to say I read Allen Browne great work on the tracking but
most of the tables do not follow the rules of his examples

each table to be audited must have an AutoNumber primary key;

You might have a tblmain that has an autonumber...gr eat
but all of the other tables like tblweekly are linked to this on a 1
to 1 or 1 to many and just using a number field .

I assume this will not work on anything except tracking changes made
to tblmain and not tblweekly

[Keith Wilby]

"sparks" <sp****@comcast .netwrote in message
news:ub******** *************** *********@4ax.c om...

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, "BeforeUpda te")

"Me" is the form object, "Me.txtIssu eNo" uniquely identifies the record and
"BeforeUpda te" 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(frmF orm As Form, lngID As Long, strTrans As
String)

'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpda te & 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.OpenRecordse t(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.SpecialEffe ct = 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.Old Value) And Not IsNull(ctl.Valu e)) Or
(IsNull(ctl.Val ue) And Not IsNull(ctl.OldV alue)) _
Or ctl.OldValue <ctl.Value Then
With rs
.AddNew
![DataSource] = frmForm.Name
![ID] = lngID
![FieldName] = ctl.ControlSour ce
![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