Approving Journal Vouchers in Ms 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:

Step 2

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

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.

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:

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.