Audit Trail using VBA

[matthewnsarah07]

I want to add an audit trail to my existing database.
Basically when certain button are clicked I want the On Click event to add an entry into tblAudit.

tblAudit contains [Request Number], [Action], [Date] [Time] [User]

I want the request number to be taken from the form field of the same name, Action will be a set phrase such as "Record Updated" and Date, Time and User just stamped e.g. Date() etc.

How would I go about writing the code to insert this record, can it be done though a query more easily?

Thanks for your help

 

[Rabbie]

There have been several threads about audit trails recently. Try searching the forum for more information

 

[matthewnsarah07]

I've looked through a few but I am looking for a way to do it exactly how I've described to make it of use

Any help please?

 

[jdraw]

I've looked through a few but I am looking for a way to do it exactly how I've described to make it of use

Any help please?

Here's a link that may help you.
http://articles.techrepublic.com.com/5100-10878_11-6166807.html

 

[TKnight]

This should work for you... It uses DAO recordsets which is a bit dated but most people find it easier to start with than ADO (which most people will probably recommend once you get the hang of it!)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

set dbs = application.currentDB
set rst = dbs.OpenRecordset("tblAudit")

With rst
.Addnew
![Request Number ] = whatever
![Action] = whatever
![Time] = Now()     ' it's better to store date and time together!
![User] = Application.CurrentUser      ' if you're using Access security
.Update
End With

set dbs = nothing
set rst = nothing

Hope this helps,

Tom