Need help with Audit Trail

This is the code I am currently using, which I got from an old post in this forum:

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 Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!RecordID = Screen.ActiveForm.CurrentRecord
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

The way things work right now is that a switchboard form is used to pull up another form that allows the user to make changes to the database/table.

How do I pull the data ID from a field in the table being updated? I'm going to use that to keep track of what is being changed.

Also, how do I implement an option so that before the updated information is saved, a pop up will appear asking you to confirm the changes with a yes or no selection? And if you select no the changes will be reverted.

Any help will be greatly appreciated, thank you.

I've got a couple of questions.

1. Currently when is the TrackChanges function being called?

2. Sorry the following doesn't make sense to me:

"How do I pull the data ID from a field in the table being updated? I'm going to use that to keep track of what is being changed."

What do you mean when you say 'data ID' are you asking about the record ID?

3. So you want to ask if they want to save the changes for every field or for the whole record?

Thanks for the quick response. As of now I got the save confirmation figured out, and my new code is:

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 Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!RecordID = Screen.ActiveForm.CurrentRecord
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With
End If

Set db = Nothing
Set rs = Nothing
End Function

As for the answers to your questions:

1. Trackchanges() is being called under Before Update every time changes are made to the fields displayed in the form called frm_X, which gets its information from the table named X. Also, the form frm_X is pulled up by another form called frm_Y.

2. In the table X, there is a field called ID, which provides an identification number for every set of information. I would like to use the identification number to track which set of information is changed by copying these numbers onto the Audit table.

Basically, this part of the code isn't working right, rs!RecordID = Screen.ActiveForm.CurrentRecord

Thank you

OK.

The easiest way that I know of to get the ID field is to add a textbox to the form and set the .visible property to false. That way the data is readily accessible and the user doesn't see it.

Assuming you just add the field to your form the line becomes:

rs!recordID = Me.ID


BTW from a user interface standpoint I would ask the user if they want to save changes BEFORE they have to enter in a reason for the change.

Last but not least I'm not sure about the:

DoCmd.RunCommand acCmdUndo

If this is run in the before update no update has occured so there's nothing to undo.

In the before update you can set Cancel = True to cancel the update so it would look something like this

field_x_BeforeUpdate (Cancel as Integer)

Cancel = Trackchanges()

end sub



Function Trackchanges () As Integer

Dim .....

Trackchanges = false

IF msgbox(...) = vbNo then

Trackchanges = True

ELSE

Inputbox ....
....

End if

Set ....

End Function


That should do it.

Thank you for the great response, I made the changes you recommended and things look much better now.

However, I still can't pull the RecordID, for some reason using "Me" has never worked for me. I have already created an ID text box with visible set to false.

Well brute force will work. <grin>

rs!recordID = Forms("<formname>").Controls("ID")

That should force it.

That worked =P. Thanks =D

I still have one more problem though, the ControlName returned by

strCtl = Screen.ActiveForm.Name

is always showing something like Option1, Option2, Switchboard, etc. Is there anyway to make it display the title of the fields that's being changed?

For example, lets say I change some of the fields in table X through frm_X, how do I log the title of the corresponding field in table X in the audit table? And if I were to change multiple fields, how do I make it log every fields that was changed?

First the easiest way to get the field name is to rename the control to match the field name then:

Screen.activeForm.Name

will return the name of the control which will be the field name.

This will work, but can be tricky in reports if you are doing anything with the field then there is confusion between the field and a control, but it should be OK here.

I'm not sure I understand your question about logging changes to all fields since I thought you were running the TrackChanges function in the beforeUpdate event of each field.
If you are running TrackChanges in the beforeUpdate of each field, and since trackChanges logs the changes then you've logged the changes for each field as I understand it.

What I meant was that, since a user can make changes to more than one field/text box in frm_X that pulls its data from table X, is there anyway that I can list the name of every field/text box that is changed? So lets say a user changed the contents of two fields/text boxes and chose to save, the ControlName in the Audit table will show "<name of field 1>, <name of field 2>".

I think the reason why it doesn't pull up the name of the right controls is because this is a form pulled up by another form. Since Option2 is the name of the control in frm_Y that brings up the frm_X where changes are being made.

So my understanding is that you want a single audit record that lists all the fields that were changed for a particular record. Correct?

This is doable, but then what are you going to do with the reason for change information? Are you going to concatenate that information as well?

Anyway if it is really what you want to do the way to do it would be to check for an existing audit record with the same record ID, username, and short date. If there is one concatenate the current field name with whatever is currently in the controlName field.

Now for the name of the field being changed, I'm assuming it is the same form_x that is being opened by all fields in form_y.

Since you're getting the name of the controls from form_y then again I think you could rename the controls in form_y to the fieldnames.

If that is not viable then another option would be to pass the field name to form_x in the openArgs part of the formOpen command so it would look something like this:

DoCmd.OpenForm "Form_x",,,,,,"<fieldName>"

Last but not least you add a textbox that isn't visible to form_x for the fieldName and then you have something like:

DoCmd.OpenForm "Form_x"
Forms("form_x")!chgField = "foo"


Hope one of these helps.

Sorry, I'm still unable to get it to work, and it seems like I've made an error and my description of how things are set up.

The database is accessed through a switchboard, which brings up the form_X that I'm writing the code for. All data presented in form_X is from fields in table_X, and I've been setting up an Audit Trial for changes made to table_X through form_X.

What I am trying to do now is to log in the fields in table_X that has been changed. For example, lets say I opened up form_X and altered the data in text box A that corresponding to the data in field A of table_X, I want the Audit Trial to list the title of field A so I can know what kind of information was changed.

Also, if I were to make changes to multiple fields, lets say fields A, B, and C. I want the Audit Trail to list the titles for all 3 fields, either one or multiple entry.

This is how my code looks like now, the field in the Audit table that should list the titles of the fields changed is FieldChanged.

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

Sub field_x_BeforeUpdate(Cancel As Integer)
Cancel = TrackChanges()
End Sub

Function TrackChanges() As Integer
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
TrackChanges = False
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbNo Then
TrackChanges = True
Else
strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveForm.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!ContractID = Forms("frm_Contracts").Controls("ID")
rs!FieldChanged = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With
End If

Set db = Nothing
Set rs = Nothing
End Function


Thanks for all your help up till now, I hope you're not getting annoyed of all my requests for help =P. My supervisor quit a while back, and I'm left to set up things with little to no knowledge of VB.

OK I think I get it.

First deciding if multiple field changes should be multiple audit records or a single audit record should be based on the needs of the users.

If the reason for changes is the same for all fields for a single record then you don't want your user to have to keep entering the reason for changes over and over again so you'll want to combine the names of all the changed fields into a single Audit record.

If when the user changes fields there are seperate reasons for changing each field then you'll want to go with multiple entries in the Audit table.

So check with the user if they want to enter reasons for each field or if they just want to enter a single reason for making changes to the record.

Now onto how to capture the field name. The easiest way I know of is to pass the field name to the function. Here is what I would suggest.


Sub field_x_BeforeUpdate(Cancel As Integer)
Cancel = TrackChanges("field_x")
End Sub

Function TrackChanges(fldName as String) As Integer

....

rs!FieldChanged = fldName

....


If the decision is for multiple audit entries then you shouldn't need to make any other changes.

If the decision if for a single audit entry with muliple fields then you can make the following changes.

Sub field_x_BeforeUpdate(Cancel As Integer)
Cancel = TrackChanges("field_x")
End Sub

Function TrackChanges(fldName as String) As Integer

...

If MsgBox(...)
...
Else
strSQL = "SELECT Audit.* FROM Audit WHERE ContractID = " & Forms("frm_Contracts").Controls("ID") & " AND dateChanged = " & Date
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.eof then

strReason = InputBox("Reason For Changes")
rs.movelast
With rs
.AddNew
rs!ContractID = Forms("frm_Contracts").Controls("ID")
rs!FieldChanged = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Else

With rs
.Edit
rs!FieldChanged = rs!FieldChanged & ", " & fldName
.Update
End With

End If

That should do it.

What do I pass as a parameter for TrackChanges in Before Update?