Audit Trail Failing after Using Tab

[amit82]

Hi team I am facing problem with the code

This Audit trail only works when row is changed I want it to work when we move from the record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As String
Dim db As Database
Dim rs As Recordset
Dim strsql As String
Dim uname As String


Access_Tracker.* FROM Singapore_Access_Tracker where Access_Tracker.[Sr No] = " & Me![Sr No]
uname = Environ("username")
strsql = "select tblaudit.* from tblaudit;"
Set db = CurrentDb()
ctl = Me.ActiveControl.Name
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!ControlName = ctl
rs!datechanged = Now()
rs!PriorINFO = Me.ActiveControl.OldValue
rs!currentinfo = Me.ActiveControl.Value
rs!CurrentUser = uname
rs!RecordID = Me.Sr_No
.Update
End With


' End If
Set db = Nothing
Set rs = Nothing
End Sub

 

[RuralGuy]

So you want to make an audit entry when the record has only been viewed but not changed?

 

[irish634]

From the sounds of it, you have a sub form with in a form and the sub form is in datasheet view. Is this correct?

If so, I am not sure if it will work if you move the code to the main form "before update" event.

If this is how you are set up, I do it the same. I call the audit trail in the sub form and each row is tracked, not the main form record. Though I track a few more things than you do (control source, form name, etc). That allows me to run my report and all I have to modify is my "strLinkCriteria" statement to match the form I am using.

My example was posted here: http://www.access-programmers.co.uk/forums/showthread.php?t=157566

Sorry if this isn't what you mean by your problem.

 

[boblarson]

The BeforeUpdate event ONLY fires if the form has been "dirtied" (has had a change of some sort to a bound field).