[RESOLVED] Track Changes in table....(MS ACCESS)

[Static]

Iok.. I need to track all changes in a table...
The only way to access the table is thru a subform hooked in...

Now in playing with the before/after updates... I see I can hit these and record the change after update.... but it will be a MAJOR pain to do this code for EACH field... not only that.. just realized that if I change one field.. move to the next in the same record.. then hit esc.. it cancels the change!

here is what I have so far.. (Just for one field)
there must be a better way to do this....

Private Type tData
    RecNum As Integer
    PrevVal As String
    NewVal As String
    FieldName As String
End Type
 
Dim FLD As tData
 
Private Sub LOGCHANGE()
    Debug.Print FLD.FieldName
    Debug.Print FLD.RecNum
    Debug.Print FLD.PrevVal
    Debug.Print FLD.NewVal
End Sub
 
 
Private Sub ADDRESS_AfterUpdate()
    FLD.NewVal = ADDRESS.Value
    FLD.FieldName = "ADDRESS"
    LOGCHANGE
End Sub
 
Private Sub ADDRESS_BeforeUpdate(Cancel As Integer)
    FLD.PrevVal = ADDRESS.OldValue
    FLD.RecNum = Me.CurrentRecord
End Sub

Thanks!

[salvelinus]

If I understand you correctly, what you want is to record some or all values of a record prior to an update, and record what changes were made. You could do this by writing to and reading from a text file.
If this is what you want, I had a similar problem that I solved by writing old values to a table before the update took place, then writing the new values to another table after the update, to maintain a synchronous log (the values are updated in the main table, of course).
This gives a table of old values and a table of new values. For my department, this is usefull because other people (aka bosses) might want to revert the changes.
If you'd like the code, no problem. Well, one problem, VBforums still doesn't always recognize me, so I may not see your reply in a timely manner.
Mods, yes, I've deleted all cookies that seem to relate to this site, several times. Recognizes me for a while, then stops.

[Static]

yes, what I want is to record each fields change... it took a bit but I created sub forms in access, and in each text field I run a function in the before/after update events. the changes are put into a table.. date/time/user/old val/new val/fieldname/recordnum seems to be working nicely.