Item ID,
Customer ID
Customer name,
Address, letters ID
Report File
1.6 Constraints and limitations
System boundaries (scope of proposed system)
The system to be developed is a customer information system, and is intended to enhance rather than replace the current system of recording item and customers records. If this proves successful, it will be possible at a future date to replace the current methods of recording records so that the details are typed directly into the computer and the required copies printed out. This could then be extended to link into a computerized accounts system.
Software
The customer has requested that the system be developed using MS Access, so unless a good reason transpired for using an alternative program or package MS Access will be the first choice of software.
Hardware
In order to run MS Access, a Pentium with a minimum of 64 MB, and preferably 128 MB, will be required. A fast processor such as a P IV will be needed if the system is not going to appear slow in switching between screens.
Mr. Wong’ employees are familiar with Word and have good keyboard skills, so they should have no problem entering data and learning how to use the system. Mr. Wong would like to improve their knowledge so that they can in the future perform new queries and reports as the need arises.
SECTION 2
DESIGN
2.1 Consideration of possible solutions
A database package will be ideal for implementing the system for Wong spare part, and as Mr. Wong has requested that it should be done in MS Access. This is the package that must be used. It would probably be possible to implement the system using MS Access or another programming language but it would take longer time and would involve the owner having to buy more software which is not really necessary, as MS Access has all the capabilities required.
I already have some experience of MS Access and it is available both at home and at college for development.
Using this package it will be possible to
- Set up the necessary tables and relationships.
- Produced customized input screens, using MS Access to automate data entry wherever possible and to perform various validations.
- Use MS Access modules to enable fast searches for a particular Customer and past stay.
- Design reports are needed.
- Implement a customized menu system.
2.2 Database design
The database contains two entities, which are CUSTOMER, ITEM and TRANSACTION.
Tables will be created for each of the entities.
Tables will be created for each of these entities.
2.3 Definition of data requirements
The tables will contain the following data.
ITEM DETAIL
CUSTOMER DETAIL
TRANSACTION DETAIL
2.4Relationship
2.5 Design of input forms
Two data entry form are needed.
Customer details
This form will be used for several purposes so it needs facilities to
- Check to see whether a Customer is already on the database.
- Add a new Customer.
- Look though all the existing records for the current Customer and bring up more details if necessary.
The Customer details form will be as shown on the next page.
Item details
This form will be used for several purposes so it needs facilities to
- Check to see whether an item is already on the database.
- Add a new item.
- Look though all the existing records for the current car and bring up more details if necessary.
The item details form will be as shown on the next page.
Customer File
Item File
2.6 Report Design
The format of all the reports will be similar. The layout of the Customer report is shown below.
2.7 Menu Design
The menu structure is as follows:
`
2.8 System Flowchart
.
2.9 Security
A password will be attached to the database so that it is only accessible to someone who knows the password. Different access levels are not needed as Mr. Wong’s employees are the only persons who will be using the database.
10 Test Strategy
The test strategy will include five different types of testing as described below:
LOGICAL TESTING
This will be used to test every aspect of each form, report and query as soon as it is implemented, using valid, invalid and extreme data. Test data will be added to test each code module and results compared with expected results. Sufficient data will be added to ensure that there is at least one customer in each category. The test data that will be added initially is shown in Appendix. Subsequent test will often involve adding new data, which will then be deleted when the test works satisfactorily.
FUNCTIONAL TESTING
Each menu item will be tested in turn to ensure that no function has been missed out.
SYSTEM TESTING
When the system is complete, the whole range of tests will be carried out again to ensure that no errors have been introduced.
RECOVERY TESTING
The computer will be re-booted while the database is open to ensure that data is not lost or corrupted in the event of a power of a power failure.
ACCEPTANCE TESTING
The user will then be involved and asked to test all the capabilities of the program to ensure that all request functions are presented and working in the manner expected. This testing may result in further refinements.
SECTION 3
TESTING
TEST PLAN
Module: Password Menu
Module: Main Menu
Module: Customer File
Module: Item File
Module: Transaction File
Test Plan
Module: Password Menu
Test No2: Inserting incorrect password
Module: Car file
Test No7: Missing car code
Module: Customer file
Test No14: Mobile Tel Number less than 7 digits
SECTION 4
SYSTEM MAINTENANCE
4.1 SYSTEM OVERVIEW
This Customer information system is designed to keep records of Customer profiles and their past purchases. It is designed to run alongside the current manual system of recording purchases, rather than replacing it. The computer will be in the reception and the owner can use it to check whether a customer who comes in is already on the database, or to check on past purchases.
New data will be added to the database at a convenient time, possibly at the end of the week. The procedure that the user will fellow is described in the design section.
4.2 TABLES AND RELATIONSHIPS
Tables and relationships were set up as specified in the design section.
4.3 FORMS
The menu structure was set up as specified in the design section.
Forms were used as follows:
Main Menu
This is specified as the start-up form and loads automatically when database is opened.
All buttons either opening other form or reports, or quitting the database, were placed using wizards.
Report Menu
All buttons placed using wizards. Maximise macro runs on opening form.
Input Forms
The Customer details form has a combo box displaying Customer codes so that the user can look up the record for any Customer. The record source for this box is the database.
4.4 REPORTS
These are as described in the user manual. All reports were created using wizards and then tailored to produce a more appropriate layout.
4.5 DISCUSSION OF TEST RESULTS
One extra report named Customer details were generated using wizards to give a hard copy of all test data used during testing. This greatly simplified the process of determining the expected output for many of the tests, and what data to use for new tests when new models were added. The report is printed in Appendix.
SECTION 5
USER DOCUMENTATION
Initial set up
Password Menu
The Password menu is automatically started when the database is loaded. The password is not case sensitive but it will only accept ‘Wong’
Main Menu
The Main Menu will automatically appear when you enter the password.
Item File
Add a new record
In order to add a new record, click on Add new and after adding all the information, click on Save.
Finding a record
To find a record, select its code from the Find record combo box.
Editing a record
To edit a record, first find it using the Find record combo box, change the information and click on Edit record.
Deleting a record
To delete a record, first find it using the Find record combo box, change the information and click on Delete record.
Customer File
Add a new record
In order to add a new record, click on Add new and after adding all the information, click on Save.
Finding a record
To find a record, select its code from the Find record combo box.
Editing a record
To edit a record, first find it using the Find record combo box, change the information and click on Edit record.
Deleting a record
To delete a record, first find it using the Find record combo box, change the information and click on Delete record.
Transaction File
Add a new record
In order to add a new record, click on Add new and after adding all the information, click on Save.
Finding a record
To find a record, select its code from the Find record combo box.
Editing a record
To edit a record, first find it using the Find record combo box, change the information and click on Edit record.
Deleting a record
To delete a record, first find it using the Find record combo box, change the information and click on Delete record.
SECTION 6
APPRAISAL
The system has been completed and installed on the user’s PC. It was completed in the manner originally designed and agreed with the user, and is straightforward to use.
Referring to the original objectives listed in the Analysis section:
1. It should take less than 30 seconds to establish whether an item or a customer is already in the database.
2. It should be possible to go directly from the Item file’s screen to the entry of a new item.
3. Data entry is as fast and easy as possible.
4. The new system provides the following information.
a) List of all item.
b) List of all customers.
c) List of all transactions.
5. The main menu should be displayed automatically when the
program is loaded and the whole system should be menu
driven.
6. The owner had some problems initially with data entry,
confusing Items and customers, as this aspect works a little differently from the manual system. However he has now entered data for over 100 items and has no problems to report.
7. All reports are implemented as specified.
8. The menus work as planned.
SECTION 7
APPENDIX
Password Menu
Option Compare Database
Private Sub Detail_Click()
End Sub
Private Sub LOGIN_Click()
On Error GoTo Err_LOGIN_Click
Dim stDocName As String
Dim stLinkCriteria As String
Pass.SetFocus
If Pass.Text = "wong" Then
stDocName = "main menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "wrong password"
End If
Exit_LOGIN_Click:
Exit Sub
Err_LOGIN_Click:
MsgBox Err.Description
Resume Exit_LOGIN_Click
End Sub
Private Sub Exit_Click()
On Error GoTo Err_Exit_Click
DoCmd.Close
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub
Main Menu
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Screen.PreviousControl.SetFocus
DoCmd.FindNext
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Screen.PreviousControl.SetFocus
DoCmd.FindNext
Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub
Private Sub Customer_File_Click()
On Error GoTo Err_Customer_File_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "customerfile"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Form_customerfile.Add_record.SetFocus
Form_customerfile.Save_record.Enabled = False
Form_customerfile.Add_record.Enabled = True
Exit_Customer_File_Click:
Exit Sub
Err_Customer_File_Click:
MsgBox Err.Description
Resume Exit_Customer_File_Click
End Sub
Private Sub Detail_Click()
End Sub
Private Sub item_file_Click()
On Error GoTo Err_item_file_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "itemfile"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Form_itemfile.Add_record.SetFocus
Form_itemfile.Save_Record1.Enabled = False
Form_itemfile.Add_record.Enabled = True
Exit_item_file_Click:
Exit Sub
Err_item_file_Click:
MsgBox Err.Description
Resume Exit_item_file_Click
End Sub
Private Sub Transaction_file_Click()
On Error GoTo Err_Transaction_file_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Transactionfile"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Form_Transactionfile.Add_record.SetFocus
Form_Transactionfile.Save_record.Enabled = False
Form_Transactionfile.Add_record.Enabled = True
Exit_Transaction_file_Click:
Exit Sub
Err_Transaction_file_Click:
MsgBox Err.Description
Resume Exit_Transaction_file_Click
End Sub
Private Sub Exit_Click()
On Error GoTo Err_Exit_Click
DoCmd.Close
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub
Item File
Option Compare Database
Private Sub Add_record_Click()
On Error GoTo Err_Add_record_Click
DoCmd.GoToRecord , , acNewRec
Com1.SetFocus
Add_record.Enabled = False
Save_Record1.Enabled = True
Exit_Add_record_Click:
Exit Sub
Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click
End Sub
Private Sub Delete_record_Click()
On Error GoTo Err_Delete_record_Click
DoCmd.GoToRecord , , acNewRec
Exit_Delete_record_Click:
Exit Sub
Err_Delete_record_Click:
MsgBox Err.Description
Resume Exit_Delete_record_Click
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Form_Load()
End Sub
Private Sub Print_Record_Click()
On Error GoTo Err_Print_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_Print_Record_Click:
Exit Sub
Err_Print_Record_Click:
MsgboxBox Err.Description
Resume Exit_Print_Record_Click
End Sub
Private Sub Item_report_Click()
On Error GoTo Err_Item_report_Click
Dim stDocName As String
stDocName = "Item report"
DoCmd.OpenReport stDocName, acPreview
Exit_Item_report_Click:
Exit Sub
Err_Item_report_Click:
MsgboxBox Err.Description
Resume Exit_Item_report_Click
End Sub
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "main menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command26_Click:
Exit Sub
Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
End Sub
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgboxBox Err.Description
Resume Exit_Command27_Click
End Sub
Private Sub Save_record1_Click()
On Error GoTo Err_Save_record1_Click
Com1.SetFocus
If Com1.Text = "" Then
MsgBox "Missing Item Code"
Else
Com2.SetFocus
If Com2.Text = "" Then
MsgBox "Missing Name"
Else
Com3.SetFocus
If Com3.Text = "" Then
MsgBox "Missing Description"
Else
Com4.SetFocus
If Com4.Text = "" Then
MsgBox "Missing Net Weight"
Else
Com5.SetFocus
If Com5.Text = "" Or Com5.Text = 0 Then
MsgBox "Missing Quantity in stock"
Else
Com6.SetFocus
If Com6.Text = "" Or Com6.Text = 0 Then
MsgBox "Missing Unit Price"
Else
Add_record.Enabled = True
Save_Record1.Enabled = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been saved"
End If
End If
End If
End If
End If
End If
Exit_Save_record1_Click:
Exit Sub
Err_Save_record1_Click:
MsgBox Err.Description
Resume Exit_Save_record1_Click
End Sub
Private Sub Combo30_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Item code] = '" & Me![Combo30] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Customer File
Option Compare Database
Private Sub Add_record_Click()
On Error GoTo Err_Add_record_Click
DoCmd.GoToRecord , , acNewRec
Com1.SetFocus
Save_record.Enabled = True
Add_record.Enabled = False
Exit_Add_record_Click:
Exit Sub
Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click
End Sub
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Screen.PreviousControl.SetFocus
DoCmd.FindNext
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
Private Sub DeleteRecord_Click()
On Error GoTo Err_DeleteRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "record has been deleted"
Exit_DeleteRecord_Click:
Exit Sub
Err_DeleteRecord_Click:
MsgBox Err.Description
Resume Exit_DeleteRecord_Click
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Form_Load()
Com1.SetFocus
Save_record.Enabled = False
Add_record.Enabled = True
End Sub
Private Sub Print_Record_Click()
On Error GoTo Err_Print_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_Print_Record_Click:
Exit Sub
Err_Print_Record_Click:
MsgBox Err.Description
Resume Exit_Print_Record_Click
End Sub
Private Sub Command22_Click()
On Error GoTo Err_Command22_Click
Dim stDocName As String
stDocName = "Customer report"
DoCmd.OpenReport stDocName, acPreview
Exit_Command22_Click:
Exit Sub
Err_Command22_Click:
MsgBox Err.Description
Resume Exit_Command22_Click
End Sub
Private Sub Customer_report_Click()
On Error GoTo Err_Customer_report_Click
Dim stDocName As String
stDocName = "Customer report"
DoCmd.OpenReport stDocName, acPreview
Exit_Customer_report_Click:
Exit Sub
Err_Customer_report_Click:
MsgBox Err.Description
Resume Exit_Customer_report_Click
End Sub
Private Sub Customer_Report1_Click()
On Error GoTo Err_Customer_Report1_Click
Dim stDocName As String
stDocName = "Customer report"
DoCmd.OpenReport stDocName, acPreview
Exit_Customer_Report1_Click:
Exit Sub
Err_Customer_Report1_Click:
MsgBox Err.Description
Resume Exit_Customer_Report1_Click
End Sub
Private Sub Back_Click()
On Error GoTo Err_Back_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "main menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Back_Click:
Exit Sub
Err_Back_Click:
MsgBox Err.Description
Resume Exit_Back_Click
End Sub
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
Com1.SetFocus
If Com1.Text = "" Then
MsgBox "Missing Customer Code"
Else
Com2.SetFocus
If Com2.Text = "" Then
MsgBox "Missing Name"
Else
Com3.SetFocus
If Com3.Text = "" Then
MsgBox "Missing Other Names"
Else
Com4.SetFocus
If Com4.Text = "" Then
MsgBox "Missing Address"
Else
Com5.SetFocus
If Com5.Text = "" Then
MsgBox "Missing Home Number"
Else
Com5.SetFocus
If Len(Com5) < 7 Then
MsgBox "Home Number must contain 7 digits"
Else
Com6.SetFocus
If Com6.Text = "" Then
MsgBox "Missing Mobile Number"
Else
Com6.SetFocus
If Len(Com6) < 7 Then
MsgBox "Mobile Number must contain 7 digits"
Else
Com7.SetFocus
If Com7.Text = "" Then
MsgBox "Missing Sex"
Else
Add_record.Enabled = True
Save_record.Enabled = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been saved"
End If
End If
End If
End If
End If
End If
End If
End If
End If
Exit_Save_Record_Click:
Exit Sub
Err_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Save_Record_Click
End Sub
Private Sub Combo27_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Code] = '" & Me![Combo27] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Transaction File
Option Compare Database
Private Sub Add_record_Click()
On Error GoTo Err_Add_record_Click
DoCmd.GoToRecord , , acNewRec
Com1.SetFocus
Add_record.Enabled = False
Save_record.Enabled = True
Exit_Add_record_Click:
Exit Sub
Err_Add_record_Click:
MsgBox Err.Description
Resume Exit_Add_record_Click
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
Com1.SetFocus
If Com1.Text = "" Then
MsgBox "Missing Transaction Code"
Else
Com2.SetFocus
If Com2.Text = "" Then
MsgBox "Missing customer Code"
Else
Com3.SetFocus
If Com3.Text = "" Then
MsgBox "Missing Item Code"
Else
Com4.SetFocus
If Com4.Text = "" Or Com4.Text = 0 Then
MsgBox "Missing Quantity"
Else
Com5.SetFocus
If Com5.Text = "" Or Com5.Text = 0 Then
MsgBox "Missing Price"
Else
Add_record.Enabled = True
Save_record.Enabled = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been saved"
End If
End If
End If
End If
End If
Exit_Save_Record_Click:
Exit Sub
Err_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Save_Record_Click
End Sub
Private Sub Delete_record_Click()
On Error GoTo Err_Delete_record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "Record has been deleted"
Exit_Delete_record_Click:
Exit Sub
Err_Delete_record_Click:
MsgBox Err.Description
Resume Exit_Delete_record_Click
End Sub
Private Sub Print_Record_Click()
On Error GoTo Err_Print_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_Print_Record_Click:
Exit Sub
Err_Print_Record_Click:
MsgBox Err.Description
Resume Exit_Print_Record_Click
End Sub
Private Sub Back_to_main_menu_Click()
On Error GoTo Err_Back_to_main_menu_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "main menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Back_to_main_menu_Click:
Exit Sub
Err_Back_to_main_menu_Click:
MsgBox Err.Description
Resume Exit_Back_to_main_menu_Click
End Sub
Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Transaction code] = '" & Me![Combo16] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Transaction_report_Click()
On Error GoTo Err_Transaction_report_Click
Dim stDocName As String
stDocName = "Transaction Report"
DoCmd.OpenReport stDocName, acPreview
Exit_Transaction_report_Click:
Exit Sub
Err_Transaction_report_Click:
MsgBox Err.Description
Resume Exit_Transaction_report_Click
End Sub