Maintain Audit trail for a table in MS access.

[mura]

Hi

I have an existing table (Students), which has students details(name , DOB,Address etc.

Every time when i make a change (Add, Edit, Delete)through a form i need the original record type field to be marked as "H". and a new record is writen to the same table with the record type marked as "A" with the changes.

any one can help me please??
thanks
Nad.

[Rabbit]

I would not create multiple records in the same table. I would create an audit table and add code in each event to insert a change record into that audit table.

[TheSmileyCoder]

As Rabbit says, it usually makes most sense to move it to a different history table. That keeps your main table running fast and efficient. You can use the BeforeUpdate and the BeforeDelete events to copy the current record into the history table.

[mura]

Since I am new to this world could you please send me the coding as you sudjest??
Many thanks.
Mura

[TheSmileyCoder]

Lets assume you have tbl_Students with the following fields:
PK_Student (Primary KEY, autonumber)
tx_StudentName (Text field)

Of course in real life you will probably have more fields.

You then add history table, I prefer to prefix mine with hist for history like so: histTbl_Student s
PK_HistoryID (Primary Key Autonumber)
FK_Student (Foreign Key, Number, Long)
tx_StudentName (Text field)

Now in the FORMS before update event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me.NewRecord then
  Dim strSQL as string
  strSQL=" INSERT INTO histTbl_Students (FK_Student,tx_StudentName) " & _
         " SELECT      tbl_Students.PK_Student, tbl_Students.tx_StudentName " & _
         " FROM        tbl_Students " & _
         " WHERE       (((tbl_Students.PK_Student)=" & Me.PK_Student & "));"
  Currentdb.Execute strSQL
End If
End Sub

This basicly means before a record is updated, the old record gets copied to the history table. To make it more detailed, I would usually have fields to record time and userID as well.

[mura]

Thank you I will try with the original table.