Another audit Trail module

[Bob H]

This is another Audit Trail I found, and would seem to better suit my
needs if it works.

The code breaks or is highlighted at this line below:

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


I created a table "tblAudit" and added the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser

Then copied and pasted the code below into a module behind the form I
want to rack the changes:


Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
'Dim strReason As String

'strReason = InputBox("Reason For Changes") I remmed this out as I don't
yet want a box coming up asking me for a reason

strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM tblAudit;"

Set db = CurrentDb()

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) <<< This line is
highlighted when have made a change to a record, then try to move to
anotherrecord


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
'rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function


Thanks

 

[Jon Lewis]

<strSQL = "SELECT Audit.* FROM tblAudit;">

Should be "SELECT tblAudit.* FROM tblAudit;" or "SELECT * FROM tblAudit;"

Jon

 

[Bob H]

Thanks for that.
It is now exactly what I want, as it records the changes in the tblAudit
and doesn't remove it as other audit trail code does.

Thanks again

 

[Bob H]

Oh, just one thing it doesn't do as I thought, was to record the form
name as per this line:
rs!FormName = Screen.ActiveForm

I don't even get any errors due to that information not being written in
the tblAudit


It is not really important, but if it is supposed to do it then I would
like it to be done.
Thanks

 

[Jon Lewis]

Try rs!FormName = Screen.ActiveForm.Name

Jon

 

[Bob H]

Thanks Jon, that does it for me now. Form name is written into the table.

 

[Bob H]

Oh dear, I've found a bug?? in the code now.

When I make a change to the data, then save the form/changes, I get a
Runtime error 2455, b ut if I don't save it, then there is no error.

This line is highlighted:

rs!RecordID = Screen.ActiveForm.CurrentRecord


With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!RecordID = Screen.ActiveForm.CurrentRecord
rs!CurrentUser = fOSUserName
'rs!Reason = strReason
.Update
End With

Thanks

 

[Douglas J. Steele]

Why do you care about CurrentRecord? As it says in the Help file, "The value
specified by this property corresponds to the value shown in the record
number box found in the lower-left corner of the form." Since that number
really doesn't mean anything, I can't think of what use saving it would be.

 

[Bob H]

I don't really care about the RecordID, but it would help me as the
admin of the database to know which records have been changed, and the
RecordID is the only way I know of among 5000 records.

But apart from that, what I really want to know is why the code is
falling over at that line, and since I remmed it out, now it falls over
at this line when I have saved a change in a record:

rs!PriorInfo = Screen.ActiveControl.OldValue

Perhaps the secret is not to save any changes and hope Access 2007 does
automatically.

Thanks