VBA Please HELP!

[shamas21]

Hi All

I really need your help. I have table1 that logs any changes made in Table 2.

So far it logs the computer name of the individual who changed a field and the time.

What I desperately need is to have one further field to log. I need to log the field within table two that was changed.

I have put in my code so far to demonstrate it logging the computer name and time.

But like I said I need the field that was changed to be logged i.e. if the field changed was ‘Customer Name’ then in the table1 I need to have the final labelling this.

Please Help!!! Thanks

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long
    Dim r As String
   
   cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "G:\Knowledge\EndUser.mdb"
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    'Output the list of all users in the current database.
 
    While Not rs.EOF
        r = rs.Fields(0)
        rs.MoveNext
    Wend
 
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1")
 
    rst.AddNew
    rst!Computer = r
    rst!Date = Now()
    rst.Update

 

[nIGHTmAYOR]

that project is big but here are your hints
1 - Your table2 should only have a serial indented key (Autonumber field) as its primary key and all fields should allow duplicates
2 - Use after insert in forms operating on table1 to always keep a copy of inserted records as a fresh start in your logging table "table2" and remember to add a numeric flag field to assign :
0 - insert
1 - update
2 - delete
3 - Use after update and before delete event in forms to insert a new copy of the record + pcname + username + time (Also note that betterly secured is to have the database hosted on a different server and assign Now() function as a default value to timestamp field and remove it from your insert statments to avoid user's attempt to change system time)
4 - use jointed select statments in reports to select all flag 1 records and relate them to flag 0 use reports conditional format feature to highlite table1.field1<>table2.field1
5 - another report to display flag 3 just where deleted records r
so now u see its merily sql less vb