Modifying the Journal Entry Form

In the previous posting, you have known already, how to create journal entry form using Form Wizard. But the result is a rough form of Journal Entry. We should reformat and add additional function in it. Now open form frmOpenTransactionJournalParent in design view, as depicted below:

Why We Should Use Parent and Child Table to Make a Journal Entry Form

There are several ways to create a table for recording transaction journal. Several software developers like to make a table that contains all the required fields in a single table of the journal, some other divide the table in 2 parts: the parent table and the child table. In essence, these two types of tables will give the same results. But in the process they are different.

Modify the Report Based on Control Setting

In previous posts, you already know how to create simple reports using the Report Button provided in the Reports Group. This posting will modify the report that was created to be displayed according to our settings like in the picture below.

Create a Report Using Report Button

Report button in Create tab - Report groups can be used to create a basic report on the data in the current table or query, to which you can add features such as groups or totals. It is very easy to apply. Point your mouse to the table the report you want to create, and click Report button. Finish.

Enter Record In the Tables Using Form

After knowing how to create table and form in MS Access, the next step is create query and report. Before doing that, you have to enter record in each table, especially master tables in the form we have created. To enter such records, make sure that you have add additional control, depicted below, on each form (put the on Form Header). If not yet, please read Control the Form Using Module and follow the steps provided there.

Control the Form Using Module

We have created a form with a simple way in which the form has not been added or installed any feature that will help us work quickly - such as print or view current form. On the other hand, we have created a module contains functions that at any time we can call in such an easy way in the objects we have created. Basically, MS Access has feature to run the function in the module we have created but unfortunately, that function can only be applied in a predetermined form, namely through the class module. In contrast with the standard module, which is created as a separate object in the Access Navigation Pane.

Create Functions using Module

In MS Access, modules contain declarations, statements, and procedures or functions that are programmed in a structured and systematic manner as a single unified entity. There are two types of MS Access module, class module and standard module. Class module is on a form or report, and usually contains a specially programmed procedures for its form or report. Standard module contains general procedures that are not associated with or can be used on other objects - such as query, form, and report. Standard modules are listed under Modules in the Navigation Pane, while a class module not.

Create a Form with Sub Form Using Form Wizard

Creating a form with a sub form in MS Access is best applied to the table that have the one-to-many relationship with another table. The objective is to eliminate redundancy in one or more fields in a table.

In general, tables that will be the form is a parent table, while the table to be used as a sub form is a child table that is relevant to the parent table. For example, forms invoices, bills, purchase orders, and various other forms that have a number of details of more than one item. Of the tables we have created, table tblOpenTransactionJournalParent will become a form with table tblOpenTransactionJournalChild that will become its sub form; table tblPermTransactionJournalParent and tblPermTransactionJournalChild are as well.

Create a Form Using Blank Form

If you want to create a form based on your own customized style, then you can create it by using Blank Form. MS Access  give much more flexibility on using Blank Form in designing a form. To create a form using Blank Form, do the following steps:

1. Click Create tab, on the Forms group click Blank Form icon. Access will display a blank form viewed in Layout View and Field List on the right.
2. Choose tblDerivativeAccount1 in the Field List. Point the mouse to the field DerivCode1. To put this field into the Layout View of the blank form, double click this field or drag it into the blank form. Do the same thing for field DerivName1.

Create a Form Using Form Wizard

Form is one of the user interface between users and database. In the case of MS Access database, the form is user interface between the user and the table. To add, delete, edit, and save a record, a user is not allowed (forbidden) to do so directly through the table. Database management systems such as MS Access, mySQL, SQL Server are an integrated database. So if we do edit a record directly in a table, other table(s) may be disturbed, so that the database may corrupt, become out of sync, or possibly be crashed. For that reasons, we should use form to add, delete, edit, and save a record. Other than that, form can be used as a navigation, guideline, even alert when an error occurred.

Create Tables Relationship

After creating and identifying the necessary tables in an accounting system, the next step is to create a relationship. Relationship describes the relations among tables in MS Access database. Once this relationship is finished, then you can design forms, reports, and queries.

A relationship works by matching data in primary key fields, usually fields with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.

Create Tables in MS Access - Transaction Table

In the accounting system, transaction table is a table containing the financial events that are recorded each time a transaction occurred based on valid evidences or documents.

The process of recording in a journal is stored in the transaction table. Generally, journals of transactions that will be created consist of two tables:

1. Parent table: contains information that is global and is a summary, for example, the ID number of the journal, the journal type, journal name maker, name of the verifier/approver
2. Child table: contains information that is details such as account codes, references, debits, credits, and descriptions of the transactions.

Create Tables in MS Access - Master Table

After identifying a glimpse of things related to the accounting system, now is the time for us to learn how MS Access works. As a starting point, we will create a very simple accounting system, the objective is that we know better what it is MS Access. Slowly but surely, we will add a variety of features that will benefit us.

Chart of Account

Chart of account is account codes and account names to be used to record transaction journal. This blog does not discuss various types of chart of accounts theory. This blog will only explain an ideal chart of account based on experience or practice and is very useful for user at the clerical level. As you know, day to day activities related with chart of account mostly used by user, in this case, employee at the clerical or staff level. At the supervisory or managerial level, chart of account is very important also, but they never involved directly in the daily recording transaction journal as the clerical staffs do. In addition, at the supervisory level and above, user usually are able to understand accounting system easily so that they can remember the account code without using any supporting tool.

The Fundamental Accounting Equation

As a rule of thumb, work in the field of accounting ranging from the knowledge base called “the fundamental accounting equation”. The fundamental accounting equation is as follows:

Assets = Liabilities + Owner’s Equities

If Owner’s Equities       =     Preferred Stocks + Common Stocks + Additions Paid in Capital + Translation FX currency + Retained Earnings
and
Equities                        =     Preferred stocks + Common Stocks + Additions Paid in Capital + Translation FX Currency
then
    Asset                        =     Liabilities + Equities + Retained Earnings
    Assets                       =    Liabilities + Equities + Revenues – Expenses
    Assets + Expenses     =     Liabilities + Equities + Revenues

Assets, liabilities, equities, expense, and revenues are called the elements of financial statement. The first three are the elements of balance sheet and the last two are the elements of income statement.

Accounting Cycle

Accounting cycle define as stages in the process of recording accounting transaction. It started from recognition of a valid document or transaction evidence and finished when accounting period is closed.

The stages of transaction recording process are as follow:

Accounting System at a Glance

As part of the information science related, today’s accounting practices can not be untied from using of information technology – mainly computer – that is used to input, process, and blend accounting data to become information that is used for decision making.
In the information concept, we know the term Input -> Process -> Output, or known as I-P-O. There is term like that also in accounting information system.