thank you all for all your suggestions. I have reached a solution on how to do it .below is the solution.you would create a global module and name it as auditchanges and insert the below code
Option Compare Database
Option Explicit
Sub AuditChangesSub(recordid As String, UserAction As String)
On Error GoTo AuditChangesSub_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim userlogin As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM audittrail", cnn, adOpenDynamic, adLockOptimistic
userlogin = getuserlogon()
Select Case UserAction
Case "new"
With rst
.AddNew
![DateTime] = Now()
![UserName] = userlogin
![FormName] = Screen.ActiveControl.Parent.Form.Name
![Action] = UserAction
![recordid] = Screen.ActiveControl.Parent.Form(recordid).Value
.Update
End With
Case "delete"
With rst
.AddNew
![DateTime] = Now()
![UserName] = userlogin
![FormName] = Screen.ActiveControl.Parent.Form.Name
![Action] = UserAction
![recordid] = Screen.ActiveControl.Parent.Form(recordid).Value
.Update
End With
Case "edit"
For Each ctl In Screen.ActiveControl.Parent.Controls
If ctl.Tag = "Audit" Then
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = Now()
![UserName] = userlogin
![FormName] = Screen.ActiveControl.Parent.Form.Name
![Action] = UserAction
![recordid] = Screen.ActiveControl.Parent.Form(recordid).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = Now()
![UserName] = userlogin
![FormName] = Screen.ActiveControl.Parent.Form.Name
![Action] = UserAction
![recordid] = Screen.ActiveControl.Parent.Form(recordid).Value
.Update
End With
End Select
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
AuditChangesSub_Err:
Exit Sub
End Sub
on the form which you want to audit the changes make sure you use the tag "Audit" on all the text boxes and combo boxes .and in the expression builder you will call the global function as below based on where you want to trigger the function,like for me below it is edit,delete and add new.
Option Compare Database
Option Explicit
Private Saved As Boolean
Private Sub clearbutton_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub cancel_Click()
Me.Undo
End Sub
Private Sub Combo131_BeforeUpdate(cancel As Integer)
If Combo131.Value = "HR" Then
Call GetCount("HR")
If hrcount = 7 Then
MsgBox "you have already reached the limit, choose another department or will be assigned"
cancel = True
Me.Combo131.Undo
End If
End If
If Combo131.Value = "IT" Then
Call GetCount("IT")
If itcount = 10 Then
MsgBox "you have already reached the limit, choose another department or will be assigned"
cancel = True
Me.Combo131.Undo
End If
End If
End Sub
Private Sub Form_BeforeUpdate(cancel As Integer)
Dim Response As Integer
If Saved = False Then
Response = MsgBox("Do you want to save the changes on this record?", vbYesNo, "Save Changes?")
If Response = vbNo Then
Me.Undo
End If
Call AuditChangesSub("ID", "edit")
Me.save.Enabled = False
End If
End Sub
Private Sub Form_Load()
Me.AllowEdits = False
End Sub
Private Sub showall_Click()
Dim strsearch As String
Call edit_Click
strsearch = "SELECT * from trainingperiod "
Me.RecordSource = strsearch
Me.txtsearch.Value = ""
End Sub
Private Sub search_Click()
Dim strsearch As String
Dim strtext As String
strtext = Me.txtsearch.Value
strsearch = "SELECT * from trainingperiod where([full name] like ""*" & strtext & "*"" or [employee id] like ""*" & strtext & "*"")"
Me.RecordSource = strsearch
Me.txtsearch.Value = ""
End Sub
Private Sub save_Click()
Call AuditChangesSub("ID", "edit")
Saved = True
DoCmd.RunCommand (acCmdSaveRecord)
Me.save.Enabled = False
Saved = False
End Sub
Private Sub edit_Click()
Me.AllowEdits = True
End Sub
Private Sub delete_Click()
Dim strsearch As String
Dim strtext As String
strtext = (Me.txtsearch.Value)
If IsNull(Me.txtsearch.Value) Then
Call edit_Click
If MsgBox("are you sure you want to delete the record", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
Call AuditChangesSub("ID", "delete")
DoCmd.RunCommand acCmdDeleteRecord
Me.Requery
End If
ElseIf MsgBox("are you sure you want to delete the record", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
Call AuditChangesSub("ID", "delete")
DoCmd.RunCommand acCmdDeleteRecord
Me.Requery
End If
Me.txtsearch.Value = ""
End Sub
Private Sub Form_Unload(cancel As Integer)
Me.Undo
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then
Response = True
End If
End Sub
Private Sub txtsearch_Click()
Call edit_Click
End Sub
Private Sub Form_Dirty(cancel As Integer)
Me.save.Enabled = True
End Sub