By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Maintain Audit trail for a table in MS access.

P: 3

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??
Oct 12 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,366
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.
Oct 12 '12 #2

Expert Mod 100+
P: 2,321
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.
Oct 12 '12 #3

P: 3
Since I am new to this world could you please send me the coding as you sudjest??
Many thanks.
Oct 12 '12 #4

Expert Mod 100+
P: 2,321
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_Students
PK_HistoryID (Primary Key Autonumber)
FK_Student (Foreign Key, Number, Long)
tx_StudentName (Text field)

Now in the FORMS before update event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2. If Me.NewRecord then
  3.   Dim strSQL as string
  4.   strSQL=" INSERT INTO histTbl_Students (FK_Student,tx_StudentName) " & _
  5.          " SELECT      tbl_Students.PK_Student, tbl_Students.tx_StudentName " & _
  6.          " FROM        tbl_Students " & _
  7.          " WHERE       (((tbl_Students.PK_Student)=" & Me.PK_Student & "));"
  8.   Currentdb.Execute strSQL
  9. End If
  10. 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.
Oct 12 '12 #5

P: 3
Thank you I will try with the original table.
Oct 12 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.