<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

How to use audit trail effectively Part 1

Published on
3,320 Points
320 Views
Last Modified:
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Christopher Hankwembo is a Zambian Chartered Accountant and also developer of CA Premier Accounting Package
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.

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)

PK
Auto   number
Unique   number
Type
Text
Type   of sales
date
Date


Created   by:
text
The   person who created it
Approved   by:
text
The   person who approved it


Table Invoice (Child)

PK
Auto   number
Unique   number
FK
Secondary   key


Product   Name
Combo
Linked   to product stocks names
Quantities
Number
Stock   sold
Price
Currency
Pricing(may   be linked to price list)
Total   Value
Currency
Total   selling price


  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.


0
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month