473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

save record and audit trail

I dont it is possible to put this codes together.. I want to prompt the user to save the record and at the same time have a log of audit trail. the codes work out fine separately.


Code for Audit Trail

Option Compare Database
Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acTextBox Then
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub

Then Insert this Code to the form to be tracked for changes

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, GenId)
End Sub



Code for Save Record

Option Compare Database
Option Explicit
Public Function SaveRecord()
On Error GoTo Err_SaveRecord
'Provide the user with the option to save/undo
'changes made to the record in the form
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
Else
DoCmd.RunCommand acCmdUndo
End If
Exit_SaveRecord:
Exit Function
Err_SaveRecord:
End Function

Any help will be highly appreciated.
Apr 20 '07 #1
2 3161
...i need help..is this possible?
Apr 23 '07 #2
nico5038
3,080 Expert 2GB
Ofcourse this is possible, but I would use a lot less code.

Start with adding a [Save] and [Cancel] button to the form.
Next test when the [Save] is pressed or all fields are filled correctly and or the user has changed something has been made like:
Expand|Select|Wrap|Line Numbers
  1. IF Me.Dirty then
  2.   ' the update code
  3. END IF
  4.  
Now issue an Append query with the unique record identifyer like:
currentdb.execute "INSERT INTO tblTraceLog (...) values (.....)"
and close the form to save the data.
Getting the idea ?

Nic;o)
Apr 23 '07 #3

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

Similar topics

6
by: Raphael Gluck | last post by:
Hi, Is it possible for one to program one's pages as such that when a database table is updated over the web, via a form, that an e-mail confirmation is sent to a specified address, notifying...
2
by: Keith | last post by:
Hi I am developing an ASP application which will interact with a SQL database. A requirement of the application is that there is a full audit trail of any modifications to data. I am...
3
by: Zlatko Matić | last post by:
Hello. I tried to implement audit trail, by making an audit trail table with the following fileds: TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName. Triggers on each table were...
1
by: Alistair Hopkins | last post by:
Hi, I am trying to write a generic audit-trail trigger function which will record changes on a field-by-field basis to a single table for all audited tables. However, I find that I can only...
13
by: Jim M | last post by:
I've been playing with Allen Browne's audit code and found it very useful. I need to track record insertions, deletions, and edits for several tables. I am planning to replace Access with Microsoft...
6
by: Parag | last post by:
Hello, I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit trails are. Our application one dedicated user...
5
by: WombatDeath | last post by:
Hi, I want my application to audit any data changes (update, insert, delete) made by the users. Rather than have an audit table mirroring each user table, I'd prefer to have a generic structure...
1
by: zadkiel | last post by:
Hi all! Recently i need to urgently perform an audit trail for my newly built system. I have gone thru many threads about using trackchanges() function at beforeupdate to record the changes made in...
4
by: Emin | last post by:
Dear Experts, We need to design a database which has an audit trail for updates to a certain set of tables. The two main approaches I've seen for this include shadow tables (an extra table to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.