Constraints:
Software
I will be using Windows XP to create the spreadsheet this is because both my home computer and the college computer operate on this system.
The software available for Foresters the end users company is Excel 2003. this version is of Excel is also accessible from my home computer and the computers from college, this then allows me to access the spreadsheet from different places when I need to work on it. Restrictions such as the end user not having the right version of Excel my occur, if this is the case they would first have to try running it on a different version e.g. Excel millennium, if the company does not have Excel at all then they would have to purchase it for the system to work.
Hardware used at college
This is the current system that I use at college is the end users computers do not meet this specification then they may have to update there version of software.
The company will have to invest in such things as a monitor, an expensive monitor is not necessary but a basic once is needed to view that actual system, also input devices, including a mouse and keyboard, these are needed to type data into the system and press buttons to view data which has already been stored. A USB Port and/or a CD Drive are required as a way of installing the program onto the end users computer, most computers already have these build in so this should not cost and extra money. A Printer may also be helpful to print out any data they need, this could include client information, or staff contact details. To perform this task they would only need a black and white standard printer. This would be cost effective and not require a lot of money to set up.
Inputs, Processing, and Output
Inputs
Processing
Outputs
Design
Test Strategy
Other than designing the system I am going to test it, this is to certify that it meets all
the end user’s requirements. By testing the system I will be able to show how
effective the system is, and whether it works or not. Testing overall allows me to find
errors before giving it to the end user so I can correct them first.
The different types of testing are:
- System Testing
- Module Testing
- Unit Testing
- Integration Testing
System Testing
This is when the whole system is tested before it is given to the user. The purpose of this is to find any defects that will only be shown when the system in completed. Therefore I will test my user form, I will make sure everything works as it is meant to, and in order for it to pass this test everything must be working.
Module Testing
When you test just the main parts of your system. You can not move onto another part of the system until each module or part of your system has be tested and corrected. In terms of my user form I will concentrate on testing the calculations and the main buttons on my opening screen. I will not move on to the next stage of testing until all these key components are working.
Unit Testing
Each part is tested by itself so that any errors are uncovered. For example on my system I will check all the validation rules are working, I will make sure you can not input the same client reference number twice on to the system. All the validation rules must be effective before I can move on to test something different.
Integration Testing
Testing of combined parts of an application to determine if they function together correctly. Usually performed after unit and functional testing. This type of testing is especially relevant to client/server and distributed systems.
his is used to test the individual processes within a solution. This type of testing can be tested by the different type of links between sheets and also data process. An example of this type of testing for my system will be testing the link between the user form, from going to one page to another, sees if it works or not. If the links between the user forms doesn’t work, this would means there is problem with the user form.
Test Plan
Implementation
Before I started my project I had to collect data from my end user, the data from Foresters included information on clients, staff and prisoners. Once I had this data I was able to start my project.
Staff Data
The screen shot above shows a list of Staff names and their contact details in a spreadsheet in Excel. This information is what is displayed below in the actual interface.
This is where the data above is shown, on the opening screen.
Client Data
Above is a list of all the client details that Foresters gave me. This includes their names, addresses, contact numbers and legal information such as when they are next due in court. This information was then shown on the client details screen on the user form. To do this I had to do an advance filter, to filter threw all the reference numbers of the clients sp that specific clients could be recognised.
Client form- Advanced Filter
Once I had got my data into a list, I started creating a user form but before I could carry this out I had to run an advanced filter. An advanced filter searched threw all the data to find, and single out the piece you want to view.
To create an advance filter I first had to copy the column headings in to three separate areas leaving enough space between each so that the required amount of data could be displayed (like below).
Once I had made a criteria range i.e. copied the headings to two other separate places, I had to run a filter with once client from the client details sheet. Too do this I when to ‘Data’/’Filter’/’Advanced Filter’ the picture below illustrates what will then appear on the screen.
I then made a macro which recorded the advanced filter. Once I had recorded the macro I found it in visual basics and matched the field source to clients on the spreadsheet and then entered this formula (below)
Range("J20").Select
Range("A8:M25").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:M2"), CopyToRange:=Range("A4:M5"), Unique:=False
This is So that when you change the clients name the other data on the form will change automatically.
Prisoners Data
These details are also filtered in the same way that the client details are, except instead of being filtered by the reference number, their filtered by each prisoner’s prison number.
This is the formula that is assigned to the macro that opens up the main interface from excel.
Sub ShowInterfaceForm()
frmOpenForesters.Show
End Sub
This opens the client page from the main page
Private Sub cmdclientinformation_Click()
frmOpenForesters.Hide
frmClients.Show
End Sub
And this formula opens the Prison details.
Private Sub frmprisonlist_Click()
frmOpenForesters.Hide
frmPrisons.Show
End Sub
Creating the Opening User Form - Step 1.
The image above shows the first stage of my interface. A Command Button and a Label have been added. I renamed the Command Button to cmdClientInformation and set the caption to Client Information. The label next to the button clear tells the user what the button does. I used a label as it is a clear yet effective way of displaying the actions of the button. The button then links to another page where the Client Information is actually displayed, this allows the user to directly find what they need from the program.
I then added another button for the prison list, an another label to display the buttons function.
To create a section to see Staff names and details I first created a Test Box, then I had to enter the correct formula into the data column under properties
.
The last button I added was to close the whole user form down, I firstly renamed the button as ‘cmdclose’ and relabelled it as close, and the next step was to enter in the right formula which is displayed above in yellow.
The final touch on the opening user form was to place a picture of
some of the staff into the form. To do this I when down to picture in
the toolbar at the side and found and selected the saved picture from
‘My Documents’.
How to create a combo box to select names
Above I have inserted a combo box; this is so that you can view a list of all the client names in Excel. You can not change the data inputted into the combo box without changing the data stored on the Excel spreadsheet and assigning a new macro all over. I chose to use this type of box because I feel it is the clearest way to view this information, also since you can’t change the data in the boxes this is helpful because it leaves less room for error.
Client Details
The first after creating a combo box is to make labels for all the titles you need, I needed nine to start with, this was to display various client information such as their address and contact number. After this I created text boxes, these where then linked via a macro to a filter carried out on the client details spreadsheet. Once the user has selected the client they want to view information on, this automatically runs a filter, links to the Marco, and then links to each individual test box to display the relevant information.
Once I had created the first nine fields I added the last three to give the end user even more of the client’s details.
Prison form
There are four different forms for each prison, (Bellmarsh, Brixton, Feltham and Holloway), each of these forms display which prisoner is in each prison and calculates when they are due to be released. To do the first task of showing which prisoner is in each prison I have created two separate filters, the first filters threw four different prisons to find the right prison. This search is done automatically when the end user clicks on the prison they want to access, the second filter, filters threw each prisoner in that prison. Two separate Marcos have been recorded, one for the prison filter and one for the prisoner filter.This way you can filter down from all the clients in a prison, to each client in each prison
-
4. Testing- 1. Search for client info
At first I had difficulty getting all the information to show, this was mainly to do with the fact that the macro had not been recorded successfully, this message was displayed and the Sub was then changed and rewritten so the function worked.
Prisons
Evaluation
General Objectives
- Create a user form to show release dates for prisoners
- To create a way of searching for client information
- To create a attractive interface that is easy to use
- Create a form that automatically shows prisoner information when the required prisoner is found
- Create four individual forms for each prison so that the task above can be performed
Limitations to my system
My system holds some limitations these are:
- You can not delete or add clients to the system directly, you have to add them to the spreadsheet in Excel
- You have to know what prison your client is in before searching for them.
- You have to know you clients name as well as there reference number before being able to see their details.
Although my system has these limitations I was still able to meet many of my general objectives. I created an operating user form that is able to do all the required tasks from the end user; these tasks where to display staff information, client information and prisoner information. My system also calculates the release date of every prisoner however you have to be able to put in the date they were imprisoned and the time in weeks the have to serve. Below is an example of what one prisoner’s release dates will look like and the formula behind the dates shown.
Quantitative objectives
- Dates calculator so date appears automatically when given certain information
- A way of checking how many clients are in certain prisons
- Links between different pages of the user form
Limitations
- Dates appear automatically but only if ‘Time in Weeks’ has been inputted by end user.
- Although you can check how many prisoners are in each prison the user form does not count for you so the end user will have to count how many names are on the list.
I have accomplished most of my quantitative objectives, my end user can check how many clients are in each prison and get easily navigate between each page of my user form. I have simply done this by adding close buttons, once you close one form you will be directed back to the last form you visited unless you are on the opening form and then you will exit the whole user form.
Qualitative objectives
- Search engine to search for clients
- To create text boxes for dates/allegations etc
- To create places to type in information e.g. name, reference number etc
- Error messages: If the user enters incorrect data, an error message must instruct the user what is wrong and what to try next
Limitations
- You search for clients by there name and no other method
- Error message function has not been finalised
My system does allow the end user to search any client on the data base how ever they can not search threw the clients by any other method, e.g., searching threw clients via their reference number. Although this is a draw back it will not be a major problem for the end user as they do not have masses of clients however if the company expands this could become a problem if the have two clients with exactly the same names. My system has a variety of text boxes and labels which display various information to my end user. A key element that is missing on my system in Error messages, this is due to a lack of time, and not managing time efficiently. Unfortunately because some functions weren’t operating smoothly less time was spend on error messages, this means that they have not be added to the system at this date but could be easily added in the future to remind the end user where not to enter certain types of data.
User Documentation
Introduction
The system is designed to assist the job of a solicitor. It does this by allowing the user to search and add clients, prisons and view staff details. The new system is an updated version of the old paper based one. The old system was very time consuming, this caused many errors to be made by staff using it.
The new system displays a list of clients that the company represent, when you find the client you are looking for on the list the client’s details are shown below automatically.
Minimum System Requirements
How to start up the system
To begin you must first open the Excel document from the C.D included with the user guide. Once you have opened it go to ‘File’ ‘Save As’ and save it to your Documents. Then open up the program and you should be looking at a screen which will be the same as the one below.
The next step is to open up the user form. This is done by pressing the large button in the middle of the screen which reads, ‘ShowInterfaceForm’. Once you have done this a screen the same as the one below will appear.
How to create a short cut on the desktop
Firstly you need to access your front desktop (as shown below)
Then click the right mouse button anywhere on the screen
The find where it says new on the list and click this, and then click shortcut
When you’ve clicked this a new window will appear
Finally click browse and find the icon you want to make into a shortcut, and then simply press next until completed
This screen is the main form.
Here you can see a list of all the staff details and two buttons leading to client information and a prison list. If you decided to press client information a form like this will then appear.
Use the drop down box on the right to select the client you are looking for, once this is done all the other boxes will be filled automatically as shown below.
Once you have finished viewing this information click close to go back, then you can select ‘Prisonlist’ you will then be show this screen.
You then select the prison you would like to search for prisoners in, for example you may want to click onto ‘Belmarsh’ in which case you will be redirected to this form.
All four of the prison forms are identical to the one above. Once you have selected the required prison you then select the prisoner you’re looking for by their surname and all the other text boxes will be filled automatically.
Finally when you are finished viewing all the information you simply press the close button, and then press it again on the main form.
Glossary of technical terms