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:
- Stock control and monitoring is efficient and effective.
- Customers’ orders are processed accurately within the shortest time possible and invoices sent in time.
- Purchase orders are processed accurately in time t~ avoid delays in items delivery.
- Data entry screens or forms are easy to use hence eliminating data entry errors.
- On-demand reports by the management are generated within the shortest time possible.
- Communication between the branches is efficient.
- Company data and information is secure from unauthorised users and only certain company employees can access certain reports.
- The overall operating cost is reduced by at least 40%.
By the end of this case study, we shall have demonstrated how to:
- Carry out the initial study.
- Carry out fact finding.
- Define system hardware and software requirements.
- Design a system using system and program flowcharts.
- 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.
- 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:
- Input data collected from transactions products, orders etc.
- Output in the form of invoices, pay vouchers etc.
- 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:
- Summary reports – Shows totals and trends such as total sales by category.
- Track stock levels and automatically alert the purchases department when restocking is necessary.
- 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:
- Improving corporate profits by 10% through reduction of bouncing orders.
- Ensuring improved customer services by efficiently managing stock levels.
- 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.
- 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:
- Studying the organisational chart.
- Quantifying work output against performance criteria.
- Observing the behaviour of the employees.
- 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.
- The constantly changing products presented to the market have created numerous internal inefficiencies and customer service problems.
- Increased customer base and sales through aggressive advertising may overload the current system’s ability to process transactions.
- Unpaid orders have increased from 4%, only two years ago, to 12%. The current credit management system has to be improved.
- Suppliers who have failed to deliver as per contracts have increased by 17% due to poor procurement procedures.
- Competition from other companies threatens the survival of the company unless there is a change in management strategies.
- Many orders are bouncing due to poor stock control. The orders that bounce are not given priority when new stock arrives.
- 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:
- Sales and customer order transaction processing.
- Inventory control and procurement processing.
Team vision
The strategic information management team has come up with the following recommendations for the new system.
- 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.
- Triple the order processing capacity of the unit by the end of fiscal year.
- Reduce order response time by 50% by the end of fiscal year.
- Rethink any underlying business processes, procedures and policies that have any visible impact on member satisfaction and complaints.
- Provide improved marketing and promotion programs.
- 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
- Schedule: The system would take 9 months to develop.
- The technology required to develop the new system is readily available in the market. .
- 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:
- 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.
- 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:
- Documentations study.
- Interviews
- Observations
- 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
- Executive summary (about 2 pages)
Recommendations
Summary of problems opportunities and alternatives
Brief statement of objectives
- Background (about 2 pages)
- Details of all the fact finding methods used e.g. interviews.
- Description analytical techniques used.
- 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.
- 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
- 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.
- 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:
- Preliminary design
- 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:
- Process customer orders which may be in form of electronic messaging, post or personal visit.
- Maintain the stock levels by automatically alerting the management when .the items go below the reorder level.
- 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:
- Output design.
- Input design.
- Files and data stores design.
- 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:
- Sharing of data among the users of the system.
- Maintaining accurate and consistent data.
- Availability of data required for current and future applications.
- Giving users easy access to data.
Designing tables /tiles structures
The following points are considered before designing a file or a table structure:
- The types of files to be constructed – is the file a master, transaction or report file?
- Data access method in the file i.e. is it random, sequential or indexed sequential. .
- 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:
- Display the table in design view.
- 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:
- Entity integrity constraint.
- Referential integrity.
- 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:
- 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.
- 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:
- The general form properties e.g. record source that is, the underlying table.
- Control properties for each of the objects.
- 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
Err–OpenProducts _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:
- Data entry and manipulation forms.
- 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:
- General report properties and data source.
- Control properties, grouping and event procedures.
- The report in design view.
- 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:
- Password protection.
- User and group permissions.
- User-level security.
- 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:
- System management – This involves installation, resource and staff allocation.
- Staff training – This is done without interfering with the business operations.
- Security control and contingency planning incase the system breaks down
- 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:
- Out of stock problems have been addressed satisfactorily.
- Stock transfer between warehouses is running smoothly.
- 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:
- Click the Start button, point to programs then click Bukuma System. A logon dialog box shown in Figure 5.36 is displayed.
- 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:
- Details processing submenus.
- 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:
- Customer entry and editing form.
- Customer orders and invoice processing.
- Preview and print invoices.
- Preview and print a list Bukuma customers.
Categories and Products
Clicking this button displays categories submenu with the following options:
- Entering and editing and categories and products details.
- Preview products per category and invoices to customers.
Suppliers
This buttons displays the supplier’s submenu with the options
- Entering new editing the supplier details and products supplied.
- Processing purchase details for items below reorder level.
- Preview and print supplier’s payments.
- 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.
