Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:
Allen Browne has an example of how to do it on his site however, and I
strongly encourage it's reading:
<http://members.iinet.net.au/~allenbrowne/AppAudit.html>
I gratefully used Allen's method for ages but found that querying it took
ages too and it also stores a lot of static data. I developed my own method
of tracking changes which I'll evetually put on my web site.
I have the following code in my form's before update event (DAO in A97):
****
Dim ctl As Control
Dim db As Database, rs As Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)
For Each ctl In Me.Detail.Controls
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
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
'MsgBox ctl.name
With rs
.AddNew
![DataSource] = "MyTable"
![ID] = Me.txtID
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
![UpdatedBy] = CurrentUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
****
The code cycles through controls with data sources and fires the update
routine when it finds one that has changed. This keeps the amount of stored
data to a minimum and you can call the data from a 'history' form if you
want.
HTH - Keith.
www.keithwilby.com 
