How to use audit trail effectively Part 1

Introduction

I believe we were all novice developers at one point and had to learn from others to gain the experience we now have, so its also our responsibility to help new developers move forward who will then, also pass their knowledge onto others that will come after them in the future.

Please take note, here I'm focusing on tracking down the generators of data entry transactions only. Not those who could have deleted or changed the controls or tables, far from that. The latter is more to do with system "audits" and that's not what I'm discussing at all.

What is an audit trail for data entry transaction processing?

In any organization which employs many people, the directors and auditors of those companies may want to know who started a certain transaction, along with the senior staff who authorized those same transactions. 

Can you imagine the trouble that can occur where the directors just have to memorize all transactions that have happened in a month and many years ago? It just wouldn't be possible. I’m sure you now understand why your system needs an audit trail! 

An audit trail accurately records all the edits that are made in transactions on a daily basis and it does not forget like a human being can forget.

How is it created?

Well, there are many methods of creating an audit trail that can be useful. Below I will show you how to create a simple audit trail. Let us assume we have a simple Invoice Table such as:

Table Invoice (Parent)

Table Invoice (Child)

1. Simply bind two forms to the tables above, parent form to the parent table and subform to the child table. The forms will help to capture data not direct in tables the easy way.
2. So to create an audit trail for the person responsible for data entry, simply put the below code in a module and call it directly behind the form, see below:
3. Make sure the control called created by is not shown on the data entry form, it must be hidden because that control will be completed by a code below.
4. Apart from the above 3 steps you do nothing
5. Just to emphasize the point, if you check in the table invoice the data entry entered via the data entry form and API will be just normal data - no difference whatsoever,
6. When generating your report, just select the controls you want to appear on the report, including the control which has the audit trail data ( Created by) that your report will have the audit trail attached. Very simple and straightforward.  

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function UserNameWindows() As String
Dim lngLen As Long
Dim strBuffer As String
Const dhcMaxUserName = 255
strBuffer = Space(dhcMaxUserName)
lngLen = dhcMaxUserName
If CBool(GetUserName(strBuffer, lngLen)) Then
UserNameWindows = Left$(strBuffer, lngLen - 1)
Else
UserNameWindows = ""
End If
End Function

• Then behind the form of the event called before, insert/do the following:

Private Sub Form_BeforeInsert(Cancel As Integer) 
Me. Created by = UserNameWindows()
End Sub

The API code will be inserting the information of the user logged into any window on the operated machine when the transaction was created. When creating the report, just include the control which has the audit trail, this way it means that when a report/document is printed, even the name of the author will be there, or showing on the report permanently for everyone to see. Doesn't that sound good?

Conclusion

Audit trails an important feature for all companies on the planet earth. Can you imagine creating a system without having an audit trail? How could you resolve problems in your organization where people aren't taking responsibility for their actions?

For part 2 we will cover how to capture the details of those who are responsible for approving transactions. Without the functionality of an audit trail, you could expect the following problems to occur:

(1) Finger pointing

(2) Quarrels amongst staff in the event of fraud

(3) Loss of company assets

If you have any questions about Part 1 of this series, you are encouraged to leave your feedback below or to view my Experts Exchange Profile page.