DEVELOPING A PROJECT USING MICROSOFT ACCESS DATABASE

Introduction

In this chapter, we undertake a case study entitled Bukuma information system. The objective of this case study is to help the student apply the system development techniques learnt in Form 3. We shall address each step that is required in developing a working information system.

5.2 . Preamble

Due to your expertise and experience in system development, you have been hired to be the head of Information Technology (IT) at Bukuma Limited, a company that runs a supermarket. Your first task is to develop a computer based information system that would ensure that:

  1. Stock control and monitoring is efficient and effective.
  2. Customers’ orders are processed accurately within the shortest time possible and invoices sent in time.
  3. Purchase orders are processed accurately in time t~ avoid delays in items delivery.
  4. Data entry screens or forms are easy to use hence eliminating data entry errors.
  5. On-demand reports by the management are generated within the shortest time possible.
  6. Communication between the branches is efficient.
  7. Company data and information is secure from unauthorised users and only certain company employees can access certain reports.
  8. The overall operating cost is reduced by at least 40%.

By the end of this case study, we shall have demonstrated how to:

  1. Carry out the initial study.
  2. Carry out fact finding.
  3. Define system hardware and software requirements.
  4. Design a system using system and program flowcharts.
  5. Construct a system that would allow:
    • Inputting of data through forms.
    • Updating, modification, deletion of existing data.
    • Carrying out of input validation and integrity checks.
    • Search or filter specific records, query and retrieve specific records.
    • Generate various transaction reports.
    • Set up database security, menus and other startup options.
  6. A sample user manual has been provided to help the student identify some components of a good user manual.

Identification and definition of the problem

The problem at hand is to develop a computer based transaction processing system (TPS) that would capture and process data about business transactions. The system should also do data maintenance i.e. provide for ability to update stored data. Some features of this system include:

  1. Input data collected from transactions products, orders etc.
  2. Output in the form of invoices, pay vouchers etc.
  3. The system is also supposed to produce a variety of reports that are to be used for managerial purposes. Such reports include inventory levels, sales reports, payroll, expenses etc.

Reports produced by the system can be classified according to purpose. These are:

  1. Summary reports – Shows totals and trends such as total sales by category.
  2. Track stock levels and automatically alert the purchases department when restocking is necessary.
  3. Produce monthly product sales and purchases reports.

In a nutshell, the following project charter outlines the objectives, preliminary investigation report and other pertinent issues that will be considered before embarking on system development:

Project charter for Bukuma information system

BUKUMA DISTRIBUTORS LTD.

             lndustrial area, Funzi Road Box 30307, NairobiKenya

Phone: 257- 020655147 Fax: 02065551418

Computerised transaction

Processing and stock control system

Project name: Bukuma computerised information system

Team leader: Jane Okindia, System analyst

Project objectives

The project development team will develop new computerised information system tl1at will support the operations of the company in order to realise the strategic vision for Bukuma products sales and delivery of customer services. It’s anticipated that the new system will provide highly integrated processes and. services that will not only reach out directly to customers and suppliers but also increase internal operational efficiency.

Therefore, improvement to the current system can result in a number of benefits. These include:

  1. Improving corporate profits by 10% through reduction of bouncing orders.
  2. Ensuring improved customer services by efficiently managing stock levels.
  3. Improved employee’s morale due to better processing and operations management. 4. Improved internal decision support so that decisions are more reliable and made on time.
  4. Support the competitive strategy of the business.

Preliminary investigation preliminary investigation and feasibility study of the current manual system and the proposed was done through:

  1. Studying the organisational chart.
  2. Quantifying work output against performance criteria.
  3. Observing the behaviour of the employees.
  4. Listening to external feedback from vendors, customers and suppliers.

By going through the investigative process, the team discovered the following problems with the current manual system.

  1. The constantly changing products presented to the market have created numerous internal inefficiencies and customer service problems.
  2. Increased customer base and sales through aggressive advertising may overload the current system’s ability to process transactions.
  3. Unpaid orders have increased from 4%, only two years ago, to 12%. The current credit management system has to be improved.
  4. Suppliers who have failed to deliver as per contracts have increased by 17% due to poor procurement procedures.
  5. Competition from other companies threatens the survival of the company unless there is a change in management strategies.
  6. Many orders are bouncing due to poor stock control. The orders that bounce are not given priority when new stock arrives.
  7. The management is not exploiting the Internet as a marketing and service delivery channel.

Scope of the system

The proposed computerised system will support the following internal business functions:

  1. Sales and customer order transaction processing.
  2. Inventory control and procurement processing.

Team vision

The strategic information management team has come up with the following recommendations for the new system.

  1. Adoption of automated data capture technology and methods e.g. using bar-coding as a means of capturing product stock records and sales, which is an automatic identification system currently being implemented in many modem business organisations.
  2. Triple the order processing capacity of the unit by the end of fiscal year.
  3. Reduce order response time by 50% by the end of fiscal year.
  4. Rethink any underlying business processes, procedures and policies that have any visible impact on member satisfaction and complaints.
  5. Provide improved marketing and promotion programs.
  6. Provide improved follow-up mechanisms for orders and backorders.

Feasibility of the proposed system

The feasibility analysis of the proposed system centered on the following:

Feasibility report

  1. Schedule: The system would take 9 months to develop.
  2. The technology required to develop the new system is readily available in the market.                                         .
  3. The system users are happy with the proposal to develop the new system.

Cost benefit analysis

Costs: The entire development and operational costs can be estimated as follows:

  • Development – Kshs 20 000 000
  • Monthly operational costs – Kshs 1 000000
  • Total lifetime of the new system – 5 years

Total lifetime costs = 20 000 000 + (12 x 1 000 000) x 5

= Kshs 80 000 000

(b) Benefits:

Profit from increased sales per month – Kshs 100 000 000

Savings from introduction of paperless mail

Kshs 1 000 000

Savings from reduced travelling costs – Kshs 20000000

Total monthly benefits.                                    Kshs 121 000000

Total lifetime benefits = Kshs (121 000000 x 12 x 5) = Kshs 7 260 000 000

Conclusion

The development of the new system is cost effective and beneficial to the company because lifetime benefits greatly outweigh the costs.

Team resolutions

In order to develop the system within the shortest time possible, the team made the following resolutions:

  1. The team will hold weekly status meetings, chaired by the project manager (system analyst). All project status meetings minutes and reports will be shared with management and all IT directors.
  2. Team members will be communicating through e-mails, dialogue and written memos to keep in touch on project development.

Conclusion

This report is intended to help the management to make decisions about undertaking the project.

Although the system seems expensive it will contribute positively towards corporate objectives when implemented in the future. Its benefits will outdo the costs by billions of shillings throughout its operational lifetime.

Thanks.

Jane Okindia System analyst.

 

Fact finding

The management has responded and given the system development team an okay to start developing the information system. The next task now is to discover user requirements of the new system through collection of facts. The members have been given the responsibility to carry out a detailed fact finding activity through the following:

  1. Documentations study.
  2. Interviews
  3. Observations
  4. Questionnaires

Documentions study

A number of manual documents were analysed. These include products catalogue, invoices, purchase orders, sales reports etc.

Interviews

Various interviews were conducted which involved the management, employees in the company, customers and suppliers. For each of the interviewee an interview guide was used.

Questionnaires

In our preliminary investigation of the business operations we decided to prepare some que.stionnaires that would help us seek some classified information without the respondent feeling threatened. Below is an extract of one of the questionnaires used.

BUKUMA DISTRIBUTORS INFORMATION SYSTEM

 

Date ………. ,…….

 

.Introduction: The IT department is in the process of developing a new information system. Please, take a few minutes to fill in this questionnaire and return it to the IT office before date………..

.

Q I. How often do customer orders bounce due to lack of enough stock?

0 Very often 0 Often 0 Not often 0 Not at all

Q2. After receiving an order, how long does it take to process it from reception to date of shipping?

0 .1 hour () Several hours  () One day () Many days

 

Fact finding summary and recommendation report

As opposed to the preliminary investigation report which dwells on problems of the existing system from the general point of view, this report gives a more detailed analysis of the system and proposals for improving or overhauling it altogether. The following is a layout outline of the report.

 

Detailed system analysis report for Bukuma distributors

                              Current system: Manual system

                        Proposed system: Computerised model

  1. Executive summary (about 2 pages)

Recommendations

Summary of problems opportunities and alternatives

Brief statement of objectives

  1. Background (about 2 pages)
    • Details of all the fact finding methods used e.g. interviews.
  • Description analytical techniques used.
  1. The current system (about 5 pages)

Models of the current system in terms of:

  • Data entry techniques e.g. form.
  • Files and data storage methods and media. (c) Informatiol1processsingandmethods used.
  1. Analysis Of the current system communication (about 5.10 pages)
    • Performance, problems, causes and opportunities.
    • Economic Problems, causes and opportunities
    • Security and control problems, causes and opportunities
    • Efficiency and service problems, causes and opportunities
  2. Detailed recommendations (about 5-10 pages)
    • Reason for the current system improvement.
    • Limiting problems (recommendations).
    • Proposed implementation plan for the new system:
      • Scope and refinement.
      • Master plan.
      • Plan for the design phase.
  1. Appendixes
    • System models.
    • Other appropriate documents.

 

System design phase

This is the most crucial system development phase. The design phase has been broken into two:

  1. Preliminary design
  2. Detailed design

Preliminary design

Preliminary design describes the functional capabilities of the proposed information system. There are many tools that can be used at this stage. However, the system flowchart tool was selected to design the system.

System flowchart

The system flowchart shows an overview of the functionality of Computerised system. Considering Bukuma distributors, the proper system required should achieve the following:

  1. Process customer orders which may be in form of electronic messaging, post or personal visit.
  2. Maintain the stock levels by automatically alerting the management when .the items go below the reorder level.
  3. Produce various transaction documents like dispatch notes pay vouchers, various expenses and sales reports and invoices.

Figure 5.2 shows a system flowchart of the proposed system.

Detailed design

Once the general functionality of the system has been designed it is now possible to design detailed designs for various system functionalities. These include:

  1. Output design.
  2. Input design.
  3. Files and data stores design.
  4. Hardware and software requirements.

Output design

This design of both screen and printed output. The output requirements for Bukuma distributors system include: Picking lists, invoices, payment vouchers, inventories, sales reports. However in this design we shall only consider invoices, payment vouchers and sales reports.

Invoices

The sample invoice below is one of the reports that the system is expected to produce after delivery of goods to a customer. Notice that it is designed carefully to display all details necessary to seal a transaction.

Input design

The quality of system input determines the quality of output. Well designed forms or screens should be effective, easy to use, attractive and consistent. The forms for data entry for Bukuma system should be clearly captioned and easy to use. Some examples of data entry forms needed for this system are invoice processing form, products entry form, : customer entry form etc. Likewise all other entry forms should be appropriately designed according to the requirements collected.

Inventory and products entry form

Categories entry form is a compound form that should allow the user to enter or view both category and products in that category. This means that the products details should be a sub form in the categories main form. Below is the lay out of the categories and products entry sub form.

Category Details

Category ID ……………….

Category Name ……………..

Description…………………..

Products details subform

Customer entry form

This is intended to be a columnar form with the following controls:

Customer ID                 Phone

Company Name            Region

Contact Name               City

Contact Title                 Country

Address

5555

Since the fields for the customers are too many for one columnar form, they can be grouped into company information and personal information. The two categories will be implemented using a tab control form.

Company info tab                   Personal info tab

Employee ID                          Address

Last Name                              City

First Name                              Country

Title                                        Date of Birth

Hire Date                                Telephone

Invoice processing entry form

This form is intended to be a compound form used to enter customer details in the main form and the items purchased in the products sub form. Bill To:

 Customer ID …………………

Order number…………………  Contact name………………..

Title …………………

Country………………

Destination name

Date ordered

Date required

Date dispatched

Freight cost

Pay voucher processing entry form

Like the invoice processing entry form, pay voucher processing is also intended to be a compound form that allows the user to enter supplier’s details in the main form and the items delivered in the products sub form. The controls in the supplier’s main form are:

Company Name                      Purchase Number

Contact Title                           Freight charges

Phone                                      Date Ordered

Country           Date Received

Suppliers entry form                 .

Supplier’s details entry form may be a two tab form with company and contact information tabs.

Company Info tab                                                 Supplier 1D

Contact tab 

Transporter entry form

Entry form for companies offering transport services to Bukuma may have the following controls:

Vehicle Number

Company Name

Postal Address

Phone

Files and data stores design

After designing the input and output, the last detailed design step is that of files or data store. The files and data store are considered the heart of any information system. There are two methods of data storage in a computer namely flat files and databases.

Flat files

In flat files, records are stored in separate files each with particular details. The use of such separate files means that the same data is often stored in more than one place

Database systems

A database is a centrally controlled store of data. It allows different users who share the database to have access to different data. This method of storage eliminates the validation problems of the flat file system.

In Bukuma Distributors Information System, the database approach is used. This will ensure that the following objectives are achieved:

  1. Sharing of data among the users of the system.
  2. Maintaining accurate and consistent data.
  3. Availability of data required for current and future applications.
  4. Giving users easy access to data.

Designing tables /tiles structures

The following points are considered before designing a file or a table structure:

  1. The types of files to be constructed – is the file a master, transaction or report file?
  2. Data access method in the file i.e. is it random, sequential or indexed sequential. .
  3. The size of the files hence the storage media to be used.

Data structures allow the system analyst to produce a view of the elements that make up the data structure. In our system, the basic data structure that will be used to store data elements of the various entities is the record.

The initial data structure design that shows only “what” the records will contain e.g. Name, Address etc. is called the logical design. This design would then eventually be converted to the physical design which specifies how the various data elements in the record will be implemented such as

Product Name as string, Product _Number as integer etc.

Below is the logical view of some of the file structures that will be designed for the Bukuma information system.

Table/file structure                                           Elements

Inventory                                           – Inventory number

  • Inventory name
  • Description

Products                                            – Product number      -Unit price

-Product name          – Purchase price

  • Quantity/unit – Quantity in stock

Customers                                         -Customer number   – Address

-Company name         – City

-Contact name            – Country

-Contact title

Supplier                                          –Supplier number – Contact title

-Company name      – Physical address

-Contact name

 

Purchase orders                             – Purchase number

  • Product purchased
  • Quantity purchased

Sales orders                                   -Order number     – Product ordered

-Customer name     – Unit price

-Order date             – Quantity

  • Required date – Discount

Transporter                                     -Transporter number -Physical address

-Company name            – Postal address

  • Address -Phone

NB: The bolded attributes will be considered as the primary keys in the database. ‘

 

Entity relationship diagram (E-R diagram)

Some of the entities that may be used in constructing Bukuma distributor’s information database are categories, products, customers, employees, orders suppliers, transporters and purchase orders. The first step is to construct the entity relationship diagram.

Interpretation

One customer may place several orders but each individual order can be placed by only one customer (one-to-many relationship). Each order placed by customers may contain many products. However, many products can be contained in one order.

Hardware and software requirements

After studying the detailed system design specifications and recommendations, the management of Bukuma decided to settle for a computer based system. This means that the development team must start looking for all the required hardware and software resources.

Setting up integrity constraints

After setting up the database based on the entity relationship diagram, some integrity constraints need to be set in the database to enhance accuracy. Examples of constrains are lookup fields and data integrity constraints.

Creating a lookup field

A lookup field displays a list of acceptable values that may be entered into a field. This avoids the problem of wrong data entry in a field. It is recommended that all foreign keys be lookup fields.

To create a lookup field:

  1. Display the table in design view.
  2. Select lookup wizard in the data type column of the desired fields. 3. Complete the prompts in the lookup wizard.

When the table is displayed in data entry mode (datasheet fonn), you simply select from the list as shown in Figure 5.6 below.

Data integrity constraints

Data integrity constraints are rules that govern entering, changing and deletion of data and records in a database. There are several types of integrity constraints. Some of them are:

  1. Entity integrity constraint.
  2. Referential integrity.
  3. Domain integrity. Entity integrity

These are the rules that govern the composition of a primary key. A primary key cannot contain a null value, it must be unique. Null values are empty spaces in a field that have no data. Figure 5.7 below shows that the Customer ID field (which is the primary key) does not allow duplicates.

Referential integrity

Referential integrity governs the nature of records in a one-to-many relationships between tables in the database. All foreign keys in the child table must have a matching record in the parent table. Referential integrity is implemented in the following ways:

  1. Restricted database- The system deletes or updates a parent record if there are no matching child A parent record is the one that has the primary key of the relationship while a child record is the one that has the foreign key of the relationship in the second table.
  2. Cascaded database- Cascaded database will delete or update all matching child records when the parent record is deleted or updated. The cascade approach is better when changing records. For example if the customer ID changes, all foreign keys in the child records are updated as well. Figure 5.8 shows how to set up referential integrity between customer and orders table in Bukuma database.

Domain integrity

In databases, the term domain is used to refer to the acceptable range of values an attribute can have. This ensures that no field takes on a value outside the range of valid values. For example in the Purchase Orders table, the date of items received is always less than or equal to the current date <=Now() as shown in the validation rule property box in Figure 5.9. Now() is a function that returns the current date.

Constructing data entry and user-interface forms

The main purpose of creating forms is to reduce data entry errors. The volume of data to be entered should be minimised and validation and integrity checks such as data type, domain and referential integrity are enforced.

This section examines creating graphical user interface (GDI) forms in Microsoft Access using the appropriate controls, macros and Visual Basic for Applications (VBA) code. Notice the code that is associated with each form and the objects on the form. In Microsoft Access most of these codes are generated by the wizard associated to each during the design. However, in some cases, general subroutines have to be written by the programmer. For the purpose of clarity, these forms are grouped into two main categories namely primary and advanced forms:

Primary forms

These are the forms primarily used to enter data into underlying tables. They include customers, categories, employees, products, suppliers, transport and employees details forms.

Advanced forms

These are more complex forms that are based on underlying queries and unbound controls. The forms provide the user with easy access to and manipulation of data in the underlying tables and other database objects.

Creating the primary forms

The main factors to be considered in creating primary forms are:

  1. The general form properties e.g. record source that is, the underlying table.
  2. Control properties for each of the objects.
  3. Command buttons and their underlying event procedures and codes.

 

Customers form

The form draws its data from the customers table. Apart from bound controls, the form has command buttons Add Customers, Search Close, and Delete. These buttons are brought to life by their underlying event procedures. The codes are generated using the command button wizard that automatically starts when you draw a command button on the form. Below are the code listings for each command button’s event procedure.

 

Option Compare Database

Private Sub CloseCustomers Click()

On Error Go To Err Close Customers Click  DoCmd. Close

Exit  CloseCustomers Click:  Exit Sub

Err Close Customers Click:

MsgBox Err.Description

Resume Exit CloseCustomers Click

End Sub

Private Sub deleterecord Click()

On Error Go To Err deleterecord Click

DoCmd.DoMenultem acFormBar, acEditMenu, 8,, acMenu Ver70 DoCmd.DoMenultem acFormBar, acEditMenu, 6,, acMenuVer70

Exit deleterecord Click:

 

    Exit Sub

Err deleterecord Click:

    MsgBox Err.Description

Resume Exit deleterecord Click

                        End Sub

Private Sub addcustomer Click()

On Error Go To Err addcustomer Click

 

 DoCmd.GoToRecord” acNewRec

Exit addcustomer Click:

 

 Exit Sub

Err addcustomer Click:

 

MsgBox Err.Description

Resume Exit addcustomer Click End Sub

Private Sub SearchRecord ClickO

On Error GoTo Err SearchRecord Click

 

Screen.PreviousControl.SetFocus

DoCmd.DoMenultem acFormBar, acEditMenu, 10,, acMenu Ver70

Exit SearchRecord Click:

 

    Exit Sub

Err SearchRecord Click:

 

MsgBox Err.Description

Resume Exit SearchRecord Click

 

    End Sub

Categories form

The categories table has many products. Hence, the fonn has the products list subfonn which shows the items in each category

The main form is a columnar form. It is populated with data from the categories table. It has command buttons New Category, Search, close and Delete. In this case, the categories are the same as the inventory ID.

The subform is a data sheet form embedded in the categories form. It gets its data from the products table.

Products form

This is a single columnar form as shown in Figure

The text box controls are populated with data from the products table. Suppliers and Category are lookup controls that source data from Supplier and Categories tables respectively. To operate the form, four command buttons used i.e. New Record, Close and Delete. Suppliers form

The suppliers form is designed using the tab tool from the toolbox. Figure 5.13 shows an illustration of the form.

The form is populated with data from the suppliers table. However, apart from the already discussed form operation buttons for adding a new record, closing a form, searching and deleting records, there is an Open Products Form button, brought to life by the OpenF orm wizard. The OpenForm event procedure code is as listed below:

Private Sub OpenProducts Click()  

On Error Go To Err OpenProducts _Click

Dim stDocName As String Dim stLinkCriteria As String stDocName = “Products”

DoCmd.OpenForm stDocName,,, stLinkCriteria

Exit OpenProducts _Click:

    Exit Sub

 ErrOpenProducts _Click:

 MsgBox Err.Description

 Resume Exit OpenProducts _Click End Sub

Transporter form

This is a single columnar form

The form is populated with data from the transporter table. The four command buttons are Add New, Delete and Close.

Creating advanced forms

As stated earlier, these are the forms which draw data from underlying queries. This section not only analyses the form properties but also gives illustrations of the underlying queries in design view and any event procedure used to automate the forms and controls.

The advanced forms are further classified into groups namely:

  1. Data entry and manipulation forms.
  2. User interface forms.

Forms for data entry and manipulation

These are the forms used for entering and manipulating data in the underlying query.

These forms include the customers invoice and suppliers’ pay voucher.

Customers invoice data entry form

This is a compound form that has the purchase orders subform contained within it

The main form is a columnar form whose record source is Customer Bills query made from two tables, Customers and Orders 

The customer details are automatically filled by selection from the lookup combo box of the customer control.

The sub form gets its data from the order amount query. The order amount query is designed using two tables, products and order details as shown in Figure 5.17. Note that the query has a calculated field i.e. Amount that calculates the amount for each product ordered based on the UnitPrice, Quality and

Discount. The expression for this field is:

Amount: ccur([order details). [unit price]*[Quantity]* (1-[Discount]/ 100)* 100

The subform has an event procedure for trapping data entry errors. This is tied to the on error event of the form properties. Since the user may forget to select a customer before entering products details in the main form section, the on error event procedure ensures that a customer is first selected before entering order details. A listing of on error event procedure is shown below.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

, Errors can occur if the user has not selected a customer for

, this order on the main form. If there is an error and Orders!BillTo is Null

6565

, inform the user and cancel the entry made in the subform.

If DataErr > 0 Then

If IsNull(Me.Parent!CustomerID) Then

MsgBox “Select a Customer to bill to before entering order details info.

RunCommand acCmdUndo

Me.Parent!CustomerID.SetFocus

        Response = acDataErrContinue

     Else

     Response = acDataErrDisplay

 End If

End If 

End Sub

The ProductID in the subform is a lookup control that enables the user to select an item from the dropdown list. This control also has an event procedure used to automatically fill the UnitPrice control from the products table once an item is selected. This minimises errors that may result from erroneous unit price entry. The code listing for the UnitPrice lookup event procedure as shown below.

Private Sub ProductID AfterUpdate()

On Error GoTo Err _ProductID AfterUpdate

 Dim strFilter As String

, Evaluate filter before it’s passed to DLookup function.

 strFilter = “ProductID = “& Me!ProductID

, Look up product’s unit price and assign it to UnitPrice control.  Me!UnitPrice = DLookup(“UnitPrice”, “Products”, strFilter) Exit _ProductID AfterUpdate:

 Exit Sub

 Err _ProductID AfterUpdate:

 MsgBox Err.Description

 Resume Exit _ProductID AfterUpdate

End Sub

Command buttons that have been used include Print Preview invoices, Print and Close. Their underlying event procedures are similar to those generated in the other forms discussed earlier.

Suppliers voucher details entry form

This is almost similar in functionality to the customers’ invoices entry form but it is intended to process payment vouchers for goods supplied to Bukuma. Figure 5.18 shows the voucher processing form.

The main form is a columnar form that derives its data from Suppliers Delivery Note query. This is created from two tables, Suppliers and PurchaseOrders tables. The supplier details are automatically filled by selecting the supplier name from the lookup combo box of the Supplier control.

The subform draws its records from an underlying query, Products Purchased shown in Figure 5.20.

The Total Puchase is a calculated control with the expression:

Total Purchase: Ccur[purchase Details].[Purchase

Price]*{QuantityOrder]* ([Purchase Discountj)/l 00) * 100

Just like in the customers’ products ordered sub form, the product ID in the products purchased subform is a lookup control from which the user selects the product name. Once a product/item is selected, the Purchase Price is automatically filled with the purchase price from the products table using the lookup event procedure shown below:

Private Sub ProductID AfterUpdate()

On Error Go To Err _ProductID _After Update

 Dim strFi/ter As String

, Evaluate filter before it’s passed to DLookup function.

 strFi/ter = “ProductID = “& Me!ProductID

, Look up product’s purchase price and assign it to PurchasePrice control. Me!PurchasePrice = DLookup(“PurchasePrice”, “Products”, strFilter)

Exit _ProductID AfterUpdate:

 Exit Sub

 Err _ProductID _AfterUpdate:

 MsgBox Err.Description

 Resume Exit _ProductID AfterUpdate End Sub

Data validation and integrity checks have been enforced so that the pay voucher cannot be processed for a null or a non-existent supplier. This is achieved using the on error event shown in the listing below.

Private Sub Form Error(DataErr As Integer, Response As Integer)

, Errors can occur if the user has not selected a supplier for

, this order on the main form. If there is an error and PurchaseOrders!PaylTo is Null , inform the user and cancel the entry made in the subform.

 If DataErr > 0 Then

 If IsNull(Me.Parent!SupplierID) Then

 MsgBox “Select a supplier to process the voucher for before entering pay details.”

RunCommand acCmdUndo

Me.Parent!SupplierID.SetFocus

Response = acDataErrContinue

Else

Response = acDataErrDisplay

EndIf

EndIf

End Sub

The command buttons used to control the form are New Voucher, Print Voucher, and Close.

User-interface forms

These are forms that enable the user to access, manipulate, display and print other forms and reports. Two such forms used in Bukuma System are sales report dialog and the main switchboard. Sales report dialog form

As shown in Figure 5.21 this form is mainly designed using unbound controls such as radio buttons, a text box and command buttons.

The three groups of controls are tied together using the code listing shown below.

Option Compare Database’ Use database order for string comparisons.

Option Explicit’ Requires variables to be declared before they are used.

Sub PrintReports(PrintMode As Integer)

On Error GoTo Err-Preview-Click

, This procedure used in Preview_Click and Print_Click Sub procedures.

, Preview or print report selected in the ReportToPrint option group.

, Then close the Print Sales Reports Dialog form.  Dim strWhereCategory As String

 strWhereCategory = “CategoryName = Forms![Sales ReportsDialog]! SelectCategory”

 Select Case Me!ReportToPrint

 Case 1

 DoCmd.OpenReport “Products stock level”, PrintMode

 Case 2

 DoCmd.OpenReport “Summary sales by date”, PrintMode

 Case 3

 DoCmd.OpenReport “Sales by category summary”, PrintMode

 Case 4

 IfIsNull(Forms![Sales Reports Dialog]!SelectCategory) Then

 DoCmd.OpenReport “Sales by Category”, PrintMode

 Else

 DoCmd.OpenReport “Sales by Category”, Print Mode, ,strWhereCategory

 End If

End Select

DoCmd.Close acForm, “Sales Reports Dialog” Exit_Preview _Click:

Exit Sub

 

Err_Preview _Click:

Resume Exit_Preview _Click End Sub Private Sub Cancel- Click() , This code created by Command Button Wizard.

On Error Go To Err Cancel Click

 

, Close form.  DoCmd. Close

Exit Cancel Click:

 

 Exit Sub

 Err Cancel Click:

 

 MsgBox Err.Description

 Resume Exit Cancel_Click

End Sub

Private Sub Preview Click()

, Preview selected report. This procedure uses the PrintReports , Sub procedure defined in (General) section of this module.

PrintReports acPreview End Sub

Private Sub Print Click()

, Print selected report. This procedure uses the PrintReports  , Sub procedure defined in (General) section of this module.

PrintReports acNormal

End Sub

Private Sub ReportToPrint AfterUpdate()

, Enable SelectCategory combo box if user selected Sales by Category

, report.

Const conSalesByCategory = 4

If MefReportToPrint. Value = conSalesByCategory Then

 MefSelectCategory.Enabled = True

Else

MefSelectCategory.Enabled = False

 End If 

End Sub

Main form (Switchboard)

The main form also referred to as the switchboard is the user-interface or main menu that is loaded when an application is started. The form mainly contains command buttons that enables the user to select the task to work on. Figure 5.22 shows the main form in design view used in Bukuma information system.

Creating reports

Reports present information to system users. These are the most visible components of a working system.

This section focuses on some of the reports expected from Bukuma information system.

The main features to be highlighted in creation of reports are:

  1. General report properties and data source.
  2. Control properties, grouping and event procedures.
  3. The report in design view.
  4. Layout of report needed.

Sales by category

This is a comprehensive report that gives sales details for each product in each category group shown in Figure 5.23.

The report draws its data from products sales query. Figure 5.24 shows the query in design view.

The report has calculated controls for a number of items sold and total sales in each category whose expressions are:

Record count:

= Summary for” & ” “ & [CategoryName] & ” (” & Count(*) & ” ” & IIf(Count(*)= 1,

“detail record”, “detail records”) & ”)”

Total sales: Sum([ Amount])

Sales by category summary

This is a compound report that has a chart subreport in it. The report gives summary total sales for each category both in tabular and graphical form

The graph shows that the most selling category is beverages followed by soaps. This report is meant for the management to decide which category of products sells more than the other.

The main report is populated with data from a select query, category sales summary

Summary sales by date

On opening this report, the user is prompted to enter the beginning and ending date. Once the valid dates are entered, the sales for the duration are displayed

The record source for the report is the products by years query. The query has a compound criteria expression in the freight date field. The expression is:

Is Not Null And Between [Beginning Date] And [Ending Date]

Purchases by category summary

Just like the Sales summary, this is also a compound report that has a pie chart subreport in it. This report gives a summary of money spent on purchases.

The main report is populated with data from a select query, products purchased shown in design view in Figure 5.28.

Products stock level

The stock report is meant to help the management make orders in good time. Figure 5.29 shows the items currently in stock and indicates those that need to be reordered.

The report draws its data from product sales query. The report is grouped by category and the stock balance is obtained using a calculated control: = ([units in stock] (sum([Quantity])))

While the reorder remark Yes or No is obtained using an if function:

= Ilf(([UnitslnStock]-(Sum([ quantity])))<[ReorderLevel], “Yes”, “No”)

 

Invoice to customers

This is a report sent to the customer showing detailed description of what is to be paid for, the total payment and the due date. Figure 5.30 shows a sample invoice to one of the customer’s ofBukuma Distributors.

The report draws its data from invoice to customers query. Figure 5.31 shows the source query in design view.

Calculated controls have also been used, these are as shown below:

 

Subtotal: = Sum([amount])

Total:= sum([amount])+ [freight]

Payment voucher

This is similar in design to the invoice, only that it is meant for the processing of payments for the suppliers of Bukuma Distributors. Figure 5.32 shows a sample pay voucher report to one of the suppliers.

The report draws its data from the products purchased voucher query. It also has an unbound image of the company logo inserted using the image tool, from the toolbox.

Calculated controls have also been used as follows: Subtotal: = Sum([Total Purchase])

Total:             = sum([Total Purchase])+ [freightCost]

Products catalogue

This is a document available to the customers and other interested persons who may want to know the items sold and the unit price of each.

The report draws data from the products table.

NB: There are other reports that may be created in a real business environment but only a few have been considered. It is the responsibility of the learner to visit a computerised business organisation to learn more about the variety of reports used.

 

Database security and startup options

To protect the information system from unauthorised access, Microsoft Access database management software, provides a number of options. These includes:

  1. Password protection.
  2. User and group permissions.
  3. User-level security.
  4. Data encryption.

To set up any of these security options, click the Tools menu, point to security and then select the type of security you want to setup.

Password protection

This is the simplest security set-up that can be implemented using any Microsoft Windows operating system. A password to open or edit the database file is set for any user who wishes to do this action.

User and group permissions

Some operating systems like Windows New Technology (NT) lets an administrator assign permission to users on database objects such as queries, forms and reports. These permissions include opening and running the object, modifying etc.

The user and group accounts dialog box is used to assign accounts to the database users. However, this can only be done by a user who is a member of the Administrators group.

User-level security

This feature is used to assign permissions to users in order to limit user access to certain database objects. For example, the order entry clerk may not have access to payroll information.

For more on setting up User Groups, Accounts and Permissions, read the online help of the Windows NT based operating systems.

Data encryption

If the database is to be used in a network or sent electronically, encryption secures the data from unauthorised access. Only the intended receipient can use the decryption key to read the message.   .

Start up options

The last step in developing a customised application is to specify the startup options. Figure 5.35 shows the startup settings for Bukuma information system.

Testing and implementing the system Testing

Individual system modules were tested using test data and found to work accurately and properly. Sample test data and error messages that were encountered due to invalid data entry are given in the appendix of this project report.

Implementation

This is the final stage in system development which involves putting the new system into use. The tasks during the implementation stage are:

  1. System management – This involves installation, resource and staff allocation.
  2. Staff training – This is done without interfering with the business operations.
  3. Security control and contingency planning incase the system breaks down
  4. Changing over from the old system to the new system using the most appropriate method.

Once the system has been installed and is running, the programming team must ensure that it is working as expected through a follow up evaluation. The evaluation report for Bukuma information system shows that apart from a few minor issues concerning the screen forms, the following issues have been addressed:

  1. Out of stock problems have been addressed satisfactorily.
  2. Stock transfer between warehouses is running smoothly.
  3. Store managers, accounts clerks and order processing managers feel that they are in full control of the system.

The programming team has promised to address the minor emergent problems encountered during the system maintenance phase.

Recommendation and conclusion

The new system received recommendation from all sectors of the organisation after the first month of operation.

Sample user manual

Below is a sample of user manual aimed at helping the users ofBukuma information system. Introduction

Bukuma information system is a computerised information system that enables users to process business transactions more efficiently and accurately.

With this system, you can prepare most of the documents that take a lot of time to prepare manually such as invoices, pay vouchers, sales reports, profit and loss accounts and many others.

Loading the system To load the program:

  1. Click the Start button, point to programs then click Bukuma System. A logon dialog box shown in Figure 5.36 is displayed.
  2. Enter your user name and password and click OK. The system main menu such as the one shown in Figure 5.37 below appears.

Menu descriptions

Within the main menu are submenus that help the user to choose on the specific task to process. These submenus are grouped into two:

  1. Details processing submenus.
  2. Viewing transaction reports.

You activate a submenu by clicking on its button. Figure 5.38 shows the customers submenu.

Description of submenu items Customer’s submenu

Once you click the customer’s button, you will get the following items:

  1. Customer entry and editing form.
  2. Customer orders and invoice processing.
  3. Preview and print invoices.
  4. Preview and print a list Bukuma customers.

Categories and Products

Clicking this button displays categories submenu with the following options:

  1. Entering and editing and categories and products details.
  2. Preview products per category and invoices to customers.

Suppliers

This buttons displays the supplier’s submenu with the options

  1. Entering new editing the supplier details and products supplied.
  2. Processing purchase details for items below reorder level.
  3. Preview and print supplier’s payments.
  4. Preview and print a list of Bukuma suppliers.

 

Viewing transaction reports submenus

In this category, you activate a submenu by clicking on its button.

Sales reports

Clicking this button displays the sales dialog box from which you can select the report to preview or print.

Purchase reports

Click this button to preview purchases made for each purchase order.

Preview catalogue

This option is available to any authorised user who wants to preview or print the list of products available for sale to any interested client.

Exiting from the program

To exit the program, simply click the Exit button located at the bottom of the main menu.

Troubleshooting data entry related errors

Invalid and erroneous data entry results to error messages being prompted on the screen. Some of the error messages that you may encounter are:

Error message: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.

Description: You are trying to enter the same item twice. Solution: Avoid double entry.

Error message: The field OrderDetails.ProductID cannot contain a null

value because the required property in the field is set to true. Description:You are trying to proceed to another record or field yet you have not selected the product name of the record in focus.

Solution: Select the product before proceeding to the next record. Error message: The value you entered isn’t valid for this field. Description: You are trying to enter an invalid data type in the field. For example, you are entering text in a numeric field or an invalid date such as 46/34/2005

Solution: Enter the correct data type.

Error message: Select a customer to bill to before entering order details info.

Description: You are trying to enter order details before selecting a customer.

Solution: Select or enter the customer before entering order or invoice details.

Incase you may experience other data entry related problems, please consult the system administrator.

Project appendix

Below is a sample appendix of the project report.

Test data used in Bukumu information system

Although a variety of test data was used to test each operation of our system, we have given only two test data tables to emphasize the need to provide test data in system development. In real situation, you are expected to provide as many test data as possible to prove the functionality of your system.

 

(Visited 132 times, 1 visits today)
Share this:

Written by