<

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

x

Approving Journal Vouchers in Ms Access Application

Published on
3,367 Points
367 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
Implementing simple internal controls in the Microsoft Access application.

Introduction:


Sometimes when we are working in a small corporation we may be asked to help improve the internal controls of an existing or a newly developed accounting system with journal entry approving levels.


To accomplish the request, we'll need to look at the following issues:


  1. The structuring of the logging in of the current accounting system.
  2. Who has access to the entire existing or new accounting package?
  3. Who has only access to data entries?
  4. Does the current system support segregation of duties?
  5. What levels are given Supervisors & Managers?


If the above issues are answered correctly and everything is in place, then our accounting system can be said to have proper segregation of duties.


Please note you cannot implement the journal voucher approval system if your accounting system has no proper segregation of duties. Ensure first your logging supports the said segregation of duties before attempting to implement this approval system.


Assuming our accounting system has a proper segregation of duties as explained above, then we now need to start implementing the discussed internal controls above.


(Approving Journal Vouchers in Ms Access Application)


Step 1


Create a new table called Authorization as shown below:




Table   Authorization

PK ID
Auto
Unique number
Approved
Text
For accepted journals
Rejected
Text
For unaccepted journals


Step 2


Let us assume the current accounting system has the journal voucher tables linked like below:





Table   Journal (Parent) Voucher Header

PK ID
Auto
Unique number
Created By
Text
The person who created the journal
Date
Date/Time
For data type definition





Table   Voucher (Child)

PK ID
Auto
Unique number
Account Code
Combo
This links the chart of accounts
Narration
Text
The nature of transaction
Debit
Number

Credit
Number


Step 3


Create an update query as shown below.  The purpose of this query is to transport the journal status, below is the said query:


Update Query (QryFinJournal)


UPDATE tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID SET tblVoucher.Authority = [Forms]![FrmJournalVourcherPosting]![CboStatus], tblJournalHeader.Authorizedby = [Forms]![FrmJournalVourcherPosting]![txtJournalAuthorized]

WHERE (((tblJournalHeader.CreateID)=[Forms]![FrmJournalVourcherPosting]![CboCreateID]));


You will notice that the above query is using the parameters from a form, below is a full sketch of the said form and VBA.

 




FrmJournalVourcherPosting

PK ID
Combo Box
Linking tblJournalHeader.CreateID
PK ID
Combo Box
Linking Table Authorization
Balance
Text
This compares both the debits & credits if the result is zero then it will allow the journal to post else the operation is cancelled.
Attach a Click Button also Here

                                                                     
Private Sub CmdPostJvs_Click()                


Now that we have the form above linking both the table authorization and table Journal Header through the following combo boxes;


  1. CboCreateID (Linking tblJournalHeader.CreateID)
  2. Cbostatus (Linking Table Authorization)


Then we are now ready to go into VBA and complete our first task. Below is the VBA:


VBA attached to FrmJournalVourcherPosting


Option Compare Database
Option Explicit
Private Sub CmdPostJvs_Click()
Dim SCh As String
Dim Cancel As Integer
If IsNull(Me.CboCreateID) Then
MsgBox "Please Select the journal to post", vbInformation, "Post Financial Journal"
Me.CboCreateID.SetFocus
Exit Sub
ElseIf IsNull(Me.Cbostatus) Then
MsgBox "Please Select Approved to post", vbInformation, "Post Financial Journal"
Me.Cbostatus.SetFocus
Exit Sub
End If
If (Me.txtbalance <> 0) Then
MsgBox "Please Check And Clear The Difference In The Box Below", vbInformation, "Post Financial Journal"
Cancel = True
Me.CboCreateID = Null
Me.Cbostatus = Null
Exit Sub
ElseIf (Me.txtbalance = 0) Then
Cancel = False
End If
SCh = "UPDATE tblJournalHeader SET Status = '" & Me.Cbostatus.Column(1) & "' WHERE [CreateID] = " & Me.CboCreateID
CurrentDb.Execute SCh, dbFailOnError
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryFinJournal"
MsgBox "Journal Posting successful", vbInformation, "Please Proceed"
Me.CboCreateID.Requery
Me.CboCreateID = Null
Me.Cbostatus.Requery
Me.Cbostatus = Null
End Sub

Step 4


Create a delete query like the one shown below.  A delete query form is required to help us delete all unapproved journals, but we have to make sure the posted journal voucher is never deleted:


Delete Query (Name = QryDeleteJvs)


DELETE tblJournalHeader.CreateID, tblJournalHeader.Status

FROM tblJournalHeader

WHERE (((tblJournalHeader.CreateID)=[Forms]![frmGeneralJournalDelete]![CboCreateID]) AND ((tblJournalHeader.Status) Is Null)) OR (((tblJournalHeader.Status)<>"Approved"));


Please check the above SQL carefully, you will notice that there are also some filters. These filters help the combo not to show posted or Journals with an approved status. To run the above delete query, you are required to create a form again as shown below:





frmGeneralJournalDelete

PK ID
Combo Box
Linking tblJournalHeader.CreateID
Attach a Click Button also Here

                                                                         


Then we have to go into VBA again, see below:


VBA


Option Compare Database
Option Explicit
 
Private Sub CmdDeleteJournal_Click()
If IsNull(Me.CboCreateID) Then
MsgBox "Please select the record to delete", vbInformation, "Record to delete Not selected"
Me.CboCreateID.SetFocus
Exit Sub
ElseIf "Approved" = DLookup("Status", "tblJournalHeader", "[CreateID]= " & Me.CboCreateID) Then
MsgBox "Please note that an approved document can never be deleted", vbInformation, "Record Cannot be deleted"
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDeleteJvs"
MsgBox "We have removed the record successfully", vbInformation, "Record Removed"
Me.CboCreateID.Requery
Me.CboCreateID = Null
End Sub
 

Finally before creating the general ledger report simply filter the underlying query as below:


SELECT tblJournalHeader.DateCreated, tblVoucher.AccountID, tblJournalHeader.Journaltype, tblJournalHeader.CreateID, tblVoucher.Dr, tblVoucher.Cr
FROM tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID
Where [Authority] = "Approved"


Final Remarks


Please ensure that the above forms are operated by a person with managerial user rights. As long as you have the proper login security features the controls discussed above can be implemented.


This article was written by Christopher Hankwembo based in Lusaka, Zambia, Southern Africa. Christopher is a qualified Chartered Certified Accountant and a Certified Internal Auditor.



0
Comment
1 Comment
LVL 55

Expert Comment

by:Gustav Brock
Please edit the article to hold the code in code blocks with normal indention to make it readable and allow for copy-paste.
0

Featured Post

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month