467,184 Members | 1,377 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,184 developers. It's quick & easy.

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.
& "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
Set ctl = Nothing
Exit Sub
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
DoCmd.RunCommand acCmdUndo
End If
Exit Function
End Function

Any help will be highly appreciated.
Apr 20 '07 #1
  • viewed: 2800
2 Replies
...i need help..is this possible?
Apr 23 '07 #2
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
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 ?

Apr 23 '07 #3

Post your reply

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

Similar topics

2 posts views Thread by Keith | last post: by
3 posts views Thread by Zlatko Matić | last post: by
6 posts views Thread by Parag | last post: by
5 posts views Thread by WombatDeath@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.