Boots Staff Payment System: Writeup

Computing Project 2: Boots Staff Payment System

Jacob Coy

Contents: 1: Analysis 1.1 Introduction:

5

1.2 Investigation:

5

1.3 The Current System:

6

How Working Hours Are Currently Stored:

6

How Extra Working Hours Added To A member Of Staffs Standard Hours:

6

Standard Working Hours For Most Staff:

6

Calculating Overtime:

6

Calculating A Member Of Staffs Pay:

6

Each Months Working Data:

6

1.4 Problems With The Current System:

7

1.5 Data Flow Diagram:

8

1.5.2 Data Flow Diagram For Old System

9

1.6 Objectives Of The New System:

10

1.7 Potential Solutions:

10

2: Design

5

11

2.1 Overall System Design

11

2.2 File/Table Design

12

Table: Staff

13

Table: Weeks

14

2.3 User Interface Design:

15

2.3.1 Forms:

15

2.3.2 Reports:

24 2

Jacob Coy

2.4 Modular Structure:

26

2.5 Testing Strategy:

27

2.6 System Security:

28

2.6.1 Password Form:

28

2.6.1.2 Psuedo code for password form:

29

Testing

30

3.1 Sample Data:

30

3.2 Test Plan:

32

3.3 Test Results:

38

Systems Maintenance

67

4.1 System Overview:

67

4.1.2 Tables:

67

4.2 Forms:

70

4.3 Reports

74

5. User Manual:

76

6. Appraisal

77

6.1 Comparison to objectives:

77

6.2 Further Development:

78

6.3 User Feedback:

79

7.0 System Code

80

Login Form:

80

Springboard Form:

81

Add and Edit Staff Form:

82

Create A New Timesheet Form:

83

Edit Weeks Form:

85 3

Jacob Coy

Generate Reports Form:

91

4

Jacob Coy

1: Analysis 1.1 Introduction: Boots is an international company selling and providing pharmaceutical products either to be sold or used in prescriptions, whilst also selling healthcare and beauty products. I currently work as a prescription dispenser for Boots store 1019 in Romsey. Contacts within the business are varied with team managers for various stores and head office in nottingham. I was initially approached by my store manager to develop a new system of monitoring members of staff’s working hours.

1.2 Investigation: Through the use of an interview with my store manager I will ascertain what the current system is capable of doing as well as what the new system is needed to perform. Listed below are the things that i will need to find out about the current system.....

• How working hours are currently stored • If any extra hours are worked how are they added to the standard working hours • What are the standard working hours for most members of staff • How is overtime calculated • How is each member of staff’s wages for the month calculated What happens to each months data once it has been processed

5

Jacob Coy

1.3 The Current System: How Working Hours Are Currently Stored: Hours staff have worked in a day are currently stored on individual pieces of paper called time sheets. These are an A4 piece of paper that contains all the staff working for the day and the hours that they are working split up into 15 minute slots, these pieces of paper are then stored in a filing cabinet until pay needs to be calculated.

How Extra Working Hours Added To A member Of Staffs Standard Hours: The timesheets are created the monday of the week that is starting, either extra hours are negotiated before this or if they work extra hours on the day extra 15 minute slots are added onto the timesheet with the letters OT (overtime)

Standard Working Hours For Most Staff: Most full time staff will work at least seven and a half hours a day with an hour and a half for breaks, although some staff such as the manager and stock supervisors will work longer to accommodate for managerial and early workers.

Calculating Overtime: overtime is calculated through looking at the timesheets for the letters OT as discussed earlier, overtime payment is general time and a half depending on the member of staffs job role and also how many hours they work normally. This is then added onto the existing pay packet shown as extra duties on their payslip.

Calculating A Member Of Staffs Pay: Using the timesheets team managers are easily able to calculate the amount of hours through the 15 minute slots a member of staff has worked and these hours are then sent to head office through the mainframe computer, then the pay department will automate payments based on the details on the system regarding hourly pay and then extra duties will be added on manually in order to not create mistakes in pay.

Each Months Working Data: all the timesheets for the month are stored in a filing cabinet with a folder for each month of the year, this is then stored for 3 months before being disposed of as head office keep a copy of the hours worked for each member of staff after this set period of time.

6

Jacob Coy

1.4 Problems With The Current System: They are several problems with the current system that is in place for example by having 7 timesheets a week and 4 sets of these for a month a lot of paperwork is created and this all has to be stored and kept safe, this is a massive burden on resources and space. Another problem that stems from having all of this paperwork is that it takes a long time to lookup a particular day of week for an individual member of staff as it all has to be sifted through. Lots of time is wasted through the use of the current system as each member of staffs day has to be written out on the timesheet and then also when the end of the month is approaching more time is spent to count the timesheets to make sure pay is allocated properly. overall this system causes a lot of problems based around organization and time, and as such a new system is required to solve these problems and allow management of the store to do their jobs properly.

7

Jacob Coy

1.5 Data Flow Diagram: Time Sheet For The Day Created

Time Sheet Used and Adjusted

Time Sheet for that day stored

Time Sheet For The Day

away

Created

This process is repeated 7 times

final days time sheet is stored with the rest of the weeks process of weeks timesheets is timesheets for the month are

repeated for each month

counted and hours work is calculated for each member of staff

payment is given

timesheets are kept for 3 months before being shredded

8

Jacob Coy

1.5.2 Data Flow Diagram For Old System

9

Jacob Coy

1.6 Objectives Of The New System: The new system is going to need to perform the following things.....

• A startup home screen with a clear function of allowing the user to navigate to the different functions of the application simply.

• A form containing all the necessary elements to modify and add members of staff and their details simply and efficiently

• Form to edit an individuals week and the hours they have worked using numbers instead of using 15 minute blocks

• The ability to create reports for a particular member of staff cross referenced by the year month week and day

1.7 Potential Solutions: I feel that the best solution to their applications is to create a computerized system using M$ Access and Visual Basic to create an application that could be run on the managers computer. I feel that they are the best solution because of Access’ ability to run multiple tables full of data which can be cross referenced and called upon easily, this can then all be output in a simple and efficient manner by Visual Basic. By combining the two applications I will be able to produce a simple user interface with a complicated back-end working system in order to create the best possible resource available to the manager at the store.

10

Jacob Coy

2: Design 2.1 Overall System Design

11

Jacob Coy

2.2 File/Table Design

12

Jacob Coy

My database will consist of 2 entity’s that share a relationship with Staff ID. Using only two entities will keep my database small and easy to manage as I will fill these out with many attributes.

Table: Staff Field Name

Type

Size (bytes)

Format

Validation

Staff ID

AutoNumber

4

Numeric-No Decimal Places

Must Be Unique

Name

Text

50

Address

Text

50

Phone

Text

255

Hourly Rate (weekdays)

Currency

8

Numeric-2 decimal Places

Hourly Rate (Sundays)

Currency

8

Numeric-2 decimal Places

Overtime

Currency

8

Numeric-2 decimal Places

Monday

Decimal

16

Numeric-2 decimal places & scale of 3

Tuesday

Decimal

16

Numeric-2 decimal places & scale of 3

Wednesday

Decimal

16

Numeric-2 decimal places & scale of 3

Thursday

Decimal

16

Numeric-2 decimal places & scale of 3

Friday

Decimal

16

Numeric-2 decimal places & scale of 3

Saturday

Decimal

16

Numeric-2 decimal places & scale of 3

Sunday

Decimal

16

Numeric-2 decimal places & scale of 3

13

Jacob Coy

Table: Weeks Field Name

Type

Size (bytes)

Format

Validation

Week Number

Number

4

Numeric-No Decimal Places

Primary Key

Year

Number

4

Numeric-No Decimal Places

Primary Key

Staff ID

Number

4

Numeric-No Decimal Places

Primary Key

Monday

Decimal

16

Numeric-2 decimal places & scale of 3

Monday Start

Date/Time

8

Short Time

Monday Finish

Date/Time

8

Short Time

Tuesday

Decimal

16

Numeric-2 decimal places & scale of 3

Tuesday Start

Date/Time

8

Short Time

Tuesday Finish

Date/Time

8

Short Time

Wednesday

Decimal

16

Numeric-2 decimal places & scale of 3

Wednesday Start

Date/Time

8

Short Time

Wednesday Finish

Date/Time

8

Short Time

Thursday

Decimal

16

Numeric-2 decimal places & scale of 3

Thursday Start

Date/Time

8

Short Time

Thursday Finish

Date/Time

8

Short Time

Friday

Decimal

16

Numeric-2 decimal places & scale of 3

Friday Start

Date/Time

8

Short Time

Friday Finish

Date/Time

8

Short Time

Saturday

Decimal

16

Numeric-2 decimal places & scale of 3

Saturday Start

Date/Time

8

Short Time

Saturday Finish

Date/Time

8

Short Time

Sunday

Decimal

16

Numeric-2 decimal places & scale of 3

Sunday Start

Date/Time

8

Short Time

Sunday Finish

Date/Time

8

Short Time

Standard Payment

Currency

8

Numeric-2 decimal places

Overtime Payment

Currency

8

Numeric-2 decimal places

14

Jacob Coy

2.3 User Interface Design:

2.3.1 Forms:

Main Startup Form: The main purpose of this form is as a springboard to navigate to other forms that perform actions that are required by Boots. Designing a form as shown below means that I am able to keep the user interface clean and allow for a simple and unified approach to the application.

15

Jacob Coy

This sketch here depicts what the initial springboard form will look like. As seen in the image it is a very simple form consisting of 4 buttons and 1 placed image with a simple background image. By not allowing the form to be maximized or enlarged I am keeping the form fixed to the size that is required in order to keep the application using a small amount of screen real estate and I believe that this is integral to keeping the application simple and useable by any member of staff no matter how much IT experience or training they have. Code Used: Because it is a springboard form it needs very little code, with all code referring to the opening of all other forms in the application itself Add Staff Button:

“ formstaff.show”

Timesheet Creation Button:

“ formtimesheet.show”

Staff’s Week Button:

“ formweek.show”

Reports Button:



“ formreport.show”

16

Jacob Coy

Timesheet Creation Form: This form is simple in construction although its main purpose is to create a new record in the database table for Weeks. The code in the background will create a record in Weeks table for every single member of staff in the database so if there are 17 members of staff contained in the Staff table then 17 records would be created for the year 2009 week 1 for example.

Year combo displays dynamically updated years based upon a set of rules and the computers clock. Week number combo displays weeks 1 to 52 which the user chooses from before clicking

Using a simple method of combo box’s to select the values required to be used in the database means that the form itself is kept to a minimal size and is kept simple with most of the work being performed by the application itself.

17

Jacob Coy

On Form Load: Year = the year of the computer Year = the current year - 3 i = the current year + 7 Display current year and 7 extra years 3 behind and 4 in front in the combo box year This is used to calculate the years that need to be available to select from the combo box year by the user. Using code like this means that the program does not have to be patched or updated each year to include the new year and extra years that might need to be used, meaning the program is useful for a prolonged period of time. Create Button: After button_click Create new rows in database table weeks for each member of staff contained in database table staff Row.staff ID = staff id pulled from table staff Row.weeknumber = week number taken from combo box week Row.year = year taken from combo box year Row.Monday = default value of 0 Row.Monday start = timeofday from computer Row.Monday finish = time of day from computer Row.Tuesday = default value of 0 Row.Tuesday start = time of day from computer Row.Tuesday finish = time of day from computer Row.Wednesday = default value of 0 Row.Wednesday start = time of day from computer Row.Wednesday finish = time of day from computer Row.Thursday = default value of 0 Row.Thursday start = time of day from computer Row.Thursday finish = time of day from computer Row.Friday = default value of 0 Row.Friday start = time of day from computer Row.Friday finish = time of day from computer Row.Saturday = default value of 0 Row.Saturday start = time of day from computer Row.Saturday finish = time of day from computer Row.Sunday = default value of 0 Row.Sunday start = time of day from computer Row.Sunday finish = time of day from computer Row.standard payment = default value of 0 Row.overtime payment = default value of 0

18

Jacob Coy

When the create button is clicked it runs through all of the code above adding values to the rows it creates in the database table weeks for each member of staff automatically pulling the Staff ID from the Staff table and then transferring that into the row Staff ID.

Staff’s Week Form: This is probably my most complicated and time consuming form to create. The form’s main purpose is to view a member of staffs week determined by the week and year chosen. This data is then shown in the text boxes on the form and you are able to edit the data and then save the data back into the database overwriting previous data for a member of staff.

Combo boxes to control what data is shown The rest of the elements on this form are text boxes and buttons to clear all the data and to save any changes to the data.

19

Jacob Coy

On Form Load:



Fill stafftableadapter with data from bootsdbdataset and table staff Dim year as integer Dim i as integer Dim counter, selindex as integer Dim datevalue as datetime = now Year = the year -3 For i = the year to the year +7 If i = dateandtime.year(datevalue) then End the if statement Counter = the counter +1 Cmbyear’s contained items are the contents of i

The code on the loading of the form is used to work out what values are required to be contained in combo box “cmbyear” it does this by reading the current date from the computers internal clock then taking 3 years off it and adding 7 years giving the values ahead of the date and the values behind the current date so you can go back in time to view weeks as well as creating weeks ahead of the current date so that you can book in staffs weeks ahead. Generate Button: Dim weeknumber as integer Dim staffid as integer Dim staffid as integer Dim i , theyear as integer Dim found as boolean = false Try Staff id = the value selected from cmbstaffid Theyear = text chosen from cmbyear Weeknumber = text chosen from cmbweek Catch ex as an exception Msgbox (“you havent selected anything”)

20

Jacob Coy

Exit sub End Try Found = false For I = 0 to form1.bootsdbdataset1.weeks.count is - 1 If staff id and weeknumber and the year is all filled then Found = true Txtmonday.text shows data from bootsdbdataset1.weeks Monday Txtmondaystart.text shows data from bootsdbdataset1.weeks Monday_start Txtmondayfinish.text shows data from bootsdbdataset1.weeks monday_finish Txttuesday.text shows data from bootsdbdataset1.weeks Tuesday Txttuesdaystart.text shows data from bootsdbdataset1.weeks Tuesday_start Txttuesdayfinish.text shows data from bootsdbdataset1.weeks Tuesday_finish Txtwednesday.text shows data from bootsdbdataset1.weeks Wednesday Txtwednesdaystart.text shows data from bootsdbdataset1.weeks Wednesday_start Txtwednesdayfinish.text shows data from bootsdbdataset1.weeks wednesday_finish Txtthursday.text shows data from bootsdbdataset1.weeks Thursday Txtthursdaystart.text shows data from bootsdbdataset1.weeks thursday_start Txtthursdayfinish.text shows data from bootsdbdataset1.weeks thursday_finish Txtfriday.text shows data from bootsdbdataset1.weeks Friday Txtfridaystart.text shows data from bootsdbdataset1.weeks friday_start Txtfridayfinish.text shows data from bootsdbdataset1.weeks friday_finish Txtsaturday.text shows data from bootsdbdataset1.weeks Saturday Txtsaturdaystart.text shows data from bootsdbdataset1.weeks saturday_start Txtsaturdayfinish.text shows data from bootsdbdataset1.weeks saturday_finish

21

Jacob Coy

Txtsunday.text shows data from bootsdbdataset1.weeks Sunday Txtsundaystart.text shows data from bootsdbdataset1.weeks Sunday_start Txtsundayfinish.text shows data from bootsdbdataset1.weeks sunday_finish Txtstandard.text shows data from bootsdbdataset1.weeks standard_payment Txtovertime.text shows data from bootsdbdataset1.weeks overtime_payment Save Data Button: Dim weeksrow as bootsdbdataset.weeksrow = form1.bootsdbdataset1.weeks.newweeks row Dim year, weeknumber, staffid as integer Year = cmbweek.text Weeknumber = cmbweek.text Weeksrow = form1.bootsdbdataset1.weeks.findbyweek_number yearStaff_ID(weeknumber,year, staffid) Weeksrow.Monday = text inputted into txtmonday Weeksrow.Monday_start = text inputted into txtmondaystart Weeksrow.Monday_finish = text inputted into txtmondayfinish Weeksrow.Tuesday = text inputted into txttuesday Weeksrow.Tuesday_start = text inputted into txttuesdaystart Weeksrow.Tuesday_finish = text inputted into txttuesdayfinish Weeksrow.Wednesday = text inputted into txtwednesday Weeksrow.wednesday_start = text inputted into txtwednesdaystart Weeksrow.wednesday_finish = text inputted into txtwednesdayfinish Weeksrow.Thursday = text inputted into txtthursday Weeksrow.Thursday_start = text inputted into txtthursdaystart Weeksrow.Thursday_finish = text inputted into txtthursdayfinish

22

Jacob Coy

Weeksrow.Friday = text inputted into txtfriday Weeksrow.Friday_start = text inputted into txtfridaystart Weeksrow.Friday_finish = text inputted into txtfridayfinish Weeksrow.Saturday = text inputted into txtsaturday Weeksrow.Saturday_start = text inputted into txtsaturdaystart Weeksrow.Saturday_finish = text inputted into txtsaturdayfinish Weeksrow.Sunday = text inputted into txtsunday Weeksrow.Sunday_start = text inputted into txtsundaystart Weeksrow.Sunday_finish = text inputted into txtsundayfinish Weeksrow.Standard_payment = text inputted into txtstandard Weeksrow.Overtime_payment = text inputted into txtovertime Update weeksrow in form1.weekstableadapter1 Clear Form Button: Clear all data in txtmonday Clear all data in txtmondaystart Clear all data in txtmondayfinish Clear all data in txttuesday Clear all data in txttuesdaystart Clear all data in txttuesdayfinish Clear all data in txtwednesday Clear all data in txtwednesdaystart Clear all data in txtwednesdayfinish Clear all data in txtthursday Clear all data in txtthursdaystart Clear all data in txtthursdayfinish Clear all data in txtfriday Clear all data in txtfridaystart Clear all data in txtfridayfinish Clear all data in txtsaturday Clear all data in txtsaturdaystart

23

Jacob Coy

Clear all data in txtsaturdayfinish Clear all data in txtsunday Clear all data in txtsundaystart Clear all data in txtstandard Clear all data in txtovertime

2.3.2 Reports: Staff ID and Name taken from staff database table.

All other data taken from Weeks database table.

24

Jacob Coy

The report runs through a query and the selection material of the query is shown below.

Attribute

Entity

Display

Criteria

Staff ID

Staff

Yes

None

Name

Staff

Yes

None

Year

Weeks

Yes

None

Week Number

Weeks

Yes

None

Monday

Weeks

Yes

None

Monday Start

Weeks

Yes

None

Monday Finish

Weeks

Yes

None

Tuesday

Weeks

Yes

None

Tuesday Start

Weeks

Yes

None

Tuesday Finish

Weeks

Yes

None

Wednesday

Weeks

Yes

None

Wednesday Start

Weeks

Yes

None

Wednesday Finish

Weeks

Yes

None

Thursday

Weeks

Yes

None

Thursday Start

Weeks

Yes

None

Thursday Finish

Weeks

Yes

None

Friday

Weeks

Yes

None

Friday Start

Weeks

Yes

None

Friday Finish

Weeks

Yes

None

Saturday

Weeks

Yes

None

Saturday Start

Weeks

Yes

None

Saturday Finish

Weeks

Yes

None

Sunday

Weeks

Yes

None

Sunday Start

Weeks

Yes

None

Sunday Finish

Weeks

Yes

None

Standard Payment

Weeks

Yes

None

Overtime Payment

Weeks

Yes

None

25

Jacob Coy

This report is available to the user as a paper based back up of data for each member of staff. This printed report can be kept for up to a year and as such they needs to be a paper based copy in a member of staffs file. Simply showing all of the data collected about that member of staff for an individual week. This is a benefit to the end user/s because it means that if they need to have a printed copy of a week they do not have to sift through the member of staffs folder as previously they can just select the year week and member of staff and have the data generated for them with minimal effort.

2.4 Modular Structure: Password Form

Springboard

Add And Edit

Create

Staff

Timesheets

Printed Staff Weekly Hours

Edit Weeks

Generate Reports

Printed Total Wages Per Week

Printed Individual Staff Yearly Wage

26

Jacob Coy

2.5 Testing Strategy: Testing the application is vital to its functionality and usefulness. I shall be testing the application after completing the database in Access. To help me with this I will be using a test plan. The major thing that is required of the application is that it is able to achieve all of its purposes without crashing which would decrease productivity and mainly its actual usefulness in the store. In order to test this I will be running through every form in the application and testing every button and element of each form making sure no problems arise that would cause a form to crash. Any problems that do arise in regards to data entry I shall be dealing with these using data validation, as this means that the program does not crash and will aid the end user in solving the problem and continuing with the program without having to reload it. After testing that all of the forms work as they are expected to and that validation is effective in solving problems and helpful to the user I will be checking that all the important functions of the application regarding maths and data is working properly. This will mean performing manual calculations and comparing these to that of data shown by the application itself, as well as making sure that data entered into the application through forms is displaying correctly in the database itself. This is important as if any of this data is wrong it could cause payments to staff to be incorrect and staff may be overpaid or underpaid this would not only lose money for boots but could also be stressful to staff involved. I shall be testing the following data entry and output functions of the program....

• Adding members of staff and editing the data • Creating a timesheet • Editing a week in a new timesheet • Reports generated have correct data and correct calculations

27

Jacob Coy

2.6 System Security: As the program is involved with sensitive data about staff and their working hours and payment it is integral that I lock down the application apart from members of staff that have the authority to look at and edit data. As such I will be creating a simple password form that will show at the startup of the program restricting all access to features until you have logged in. I am keeping this form simple as it is not hugely required to have a username as they will only ever really be 2 members of staff editing and viewing the data contained within. Keeping it contained to just one password is a lot simpler for staff as well as to code.

2.6.1 Password Form:

28

Jacob Coy

Shown above is the design for the password form. As discussed earlier it is a simple form with few elements, running simply using a few if statements and validation rules the end user just has to enter the correct password and hit “Login To The System”. The text box used to enter the password is formatted with an input mask showing the password as a series of asterix’s e.g. Passwordtest would be ************ . By adding in validation to the form so that if the password is entered incorrectly 5 times the entire program closes (after a msgbox appears) means that the program has a more secure nature.

2.6.1.2 Psuedo code for password form:



Dim passwordprotector As Integer

If text in text box = “passwordtest” Then Open Springboard form Hide this form Disable this form Else Show message box saying “sorry that was an incorrect password”) Clear the text box Passwordprotector = add 1 to password protector If password protector = 5 Then Show message box saying “you have entered the password incorrectly 5 times. Application is closing” Close this form End If End If

29

Jacob Coy

3. Testing 3.1 Sample Data: Shown below are samples of data that is held in the tables of my database before testing has started. Table: Staff: The Staff table holds data about each member of staff that works for the store. By defining Staff ID as the Primary Key it stops data duplication from happening as easily also giving each individual member of staff added to the database a unique identifier. This data is generally only known to 3 people. 1) the member of staff. 2) the store manager. and 3) The staff manager. Although only the store manager and staff manager will have access to this data though the password form.

30

Jacob Coy

Table Weeks: This is my second and final table. The Weeks table contains all the data associated with an individual week in a year by each member of staff in the database. These tables are not created in the database itself but are created by the application on a specially designed form. Primary keys in this table are Week Number, Year and Staff ID. Using a combination of the 3 means that I will struggle to create a week for a specific year and member of staff more than once, which if I did would falsify the data contained within this table.

31

Jacob Coy

3.2 Test Plan: No

1

Area Tested

Test Data

Description Purpose

Expected Outcome

P a s s w o r d P a s s w o r d = Check whether if you Main springboard form Form “passwordtest” e n t e r t h e c o r r e c t will open and t he

Output Reference

Page #37

password it lets you into password from will be the main program 2

hidden

P a s s w o r d P a s s w o r d = C h e c k i n g t o s e e i f Message box should Form “suck" entering an incorrect appear to inform that

page #39

password stops you an incorrect password from entering the main has been entered springboard form. 3

P a s s w o r d P a s s w o r d = C h e c k i n g t o s e e i f Message box should Form “suck” X5 entering an incorrect appear informing that

page #40

password 5 or more they have entered the times stops you from password incorrectly 5 entering m o r e times and then close passwords.

4

the program on clicking ok.

M a i n Springboard

Check that the button Add and edit staff form opens the correct form should open and not

Form (add and

a n d f a d e s o u t t h e let you select from the

edit staff button)

springboard form not springboard form. letting you go back to it

page #40

till closing the form open. 5

M a i n Springboard

Check that the button C r e a t e a n e w opens the correct form timesheet form should

Form (create a

a n d f a d e s o u t t h e open and not let you

new timesheet button)

springboard form not s e l e c t f r o m t h e letting you go back to it springboard form.

page #41

till closing the form open. 6

M a i n Springboard

Check that the button View a member of opens the correct form s t a f f ’s w e e k f o r m

Form (View a

a n d f a d e s o u t t h e should open and not

member of s t a f f ’s w e e k

springboard form not let you select from the letting you go back to it springboard form.

button)

till closing the form open.

page #41

32

Jacob Coy No

Area Tested

7

M a i n Springboard

Check that the button Generate a report on a opens the correct form member of staff form

Form (Generate

a n d f a d e s o u t t h e should open and not

a report on a member of staff

springboard form not let you select from the letting you go back to it springboard form.

button)

till closing the form open.

Add and Edit Staff ID1 Staff form

Check that staff ID one Data for staff member shows data and it is “betty” should show

8

Test Data

Description Purpose

Expected Outcome

Output Reference

page #42

page #43

correct with data shown and be the same as in in the database table for the database. staff. 9

Add And Edit N e w S t a f f Test to check if new Once the + button has Staff Form Button. Name staff can be added to been clicked a new =

page #44

“ j o h n ” the database. Data s t a f f I D w i l l b e

a d d r e s s = fields filled out and then generated and then “johns house” hit save. data will need to be phone = “02380457845

filled in. After which hitting save should

” weekdays £ =

save all this data into

“1.50” Sunday £ = “3.00”

the database and be avaliable in the

overtime £ = “7.00” Monday

program.

= “1” Tuesday = “2” Wednesday = “3” Thursday = “4” Friday = “5” Saturday = “6” Sunday = “7” Then click save button 10 Add And Edit Staff Form

Test to see if staff can Once John has been be deleted from the found by clicking the database successfully.

page #45

arrows on the top right of the control bar clicking the X in the bar should delete John from the database and the program.

33

Jacob Coy No

Area Tested

Test Data

Description Purpose

Expected Outcome

11 Create a New Year = 2007 Test to see if a new After clicking create T i m e s h e e t week number t i m e s h e e t c a n b e the timesheet should Form

= 50

created successfully.

Output Reference

page #47

b e c re a t e d i n t h e database and you can then edit this timesheet in the editweeks form

12 Create a New Timesheet

Test to check that the Current year with 3 dates in Year are correct years behind and 4

F o r m ( Ye a r

with the algorithm and ahead and 52 weeks

Combobox

t h a t t h e r e a r e t h e to choose from in the correct amount of week weeks combobox.

Week Number

numbers.

Combobox)

13 Create a New Y e a r Test to make sure I Typing anything into T i m e s h e e t Combobox “a” cannot enter data into the combobox should Form

Week Number

closest number value if numbers are entered or nothing if text is entered.

14 Create a New Y e a r Making sure that both Message box should T i m e s h e e t Combobox = comboboxes have data appear for either one “”

page #49

i n s i d e t h e m b e f o re telling the user that

creating Week Number timesheet. Combobox “1” & Y

page #49

the comboboxes with e i t h e r s e l e c t t h e

the use of a keyboard Combobox that would contradict “54” with the data available to choose from.

Form

page #48

e

a

a

n e w they cannot create a timesheet until they have chosen data from both comboboxes.

r

combobox = “2009” Week number combobox “” 15 Create a New Y e a r M a k i n g s u r e t h a t Message box should T i m e s h e e t Combobox = duplicate data is not appear telling the user Form

“2009” Week Number Combobox “1”

created.

page #52

that the year and week that they have created has already been created beforehand.

34

Jacob Coy No

Area Tested

Test Data

Description Purpose

Expected Outcome

16 Edit Weeks Form (name

Test to make sure that All names from staff all of the staff in the database are shown in

combobox,

database are shown in the drop down menu

year combobox.

the combobox for name as well as the correct as well as the years years and 52 weeks in

Week number combobox_

being correct (3 behind, a year. 4 ahead of the current

Output Reference

page #52

year) and 52 weeks in weeks combobox 17 Edit Weeks Form

Year = “2006” Week number = “50”

Making sure that you Message box should cannot edit a timesheet appear to instructing

page #53

that does not exist in the t h e u s e r t h a t t h e database.

criteria they have chosen from does not exist in the database yet.

18 Edit Weeks Form

Name = “Mark” C h e c k i n g t h a t d a t a Data should be exactly Year = “2009 “ shown in the text boxes the same as in the

page #54

week number is identical to that in the weeks table. = “1” 19 Edit Weeks Form (save

database. Checking that clicking Once data is saved it t h e s a v e c h a n g e s should be updated

changes

button saves the data and be identical in the

button)

edited in the form back database to the database.

20 Edit Weeks Form (clear form button)

C h e c k i n g t o s e e i f All text boxes should clicking the clear form be cleared back to

page #55

page #57

button clears all the data their default empty “” in the text boxes on the state. form.

35

Jacob Coy No

Area Tested

21 Edit Weeks Form

Test Data

Description Purpose

Expected Outcome

M e m b e r o f Test to make sure that Message box should staff = “betty” the application checks appear informing the

Output Reference

page #59

year = “2009” that you have entered user that they have not week number something in all the text entered data for all text = “1” Monday b o x e s o n t h e f o r m boxes on the form. = “” Monday before allowing you to start = “00:00” s a v e b a c k t o t h e Monday finish database. = “00:00” Tuesday = “2” Tuesday Start = “10:00” Tuesday finish = “12:00” Wednesday = “1” Wednesday start = “12:00” Wednesday finish = “13:00” Thursday = “3” Thursday start = “12:00” Thursday finish = “15:00” Friday = “1” Friday start = “14:00” Friday finish = “15:00” Saturday = “” Saturday start = “00:00” Saturday finish =

“00:00”

Sunday = “0” Sunday start = “0:00” Sunday finish = “00:00” s t a n d a r d payment = “5.00” overtime payment = “7.50”

36

Jacob Coy No

Area Tested

22 Generate reports form

Test Data

Description Purpose

Expected Outcome

S t a ff w e e k l y Testing to see if the Message box appears hours: Name = application will let you telling the user that

Output Reference

page #60

“betty”, year = g e n e r a t e a r e p o r t they have no selected “”, w e e k without first picking all data required for a number = “50” the information required report from the drop per report 23 Generate reports form

down menus.

t o t a l w e e k l y Testing to see if the Message box appears wage: year = application will let you telling the user that

page #60

“2009”, week g e n e r a t e a r e p o r t they have no selected number = “”

without first picking all data required for a the information required report from the drop per report

24 Generate reports form

down menus.

Individual year Testing to see if the Message box appears Wages: Name application will let you telling the user that

page #61

= “betty”, Year g e n e r a t e a r e p o r t they have no selected = “”

without first picking all data required for a the information required report from the drop per report

25 Generate reports form

down menus.

Staff Weekly Testing to see if the Report should print Hours: Name = information displayed on w i t h a l l c o r r e c t

page #61

“betty”, year = the report is correct to information displayed “2009”, week the data in the table number = “2” weeks contained in the database 26 Generate reports form

To t a l Wa g e s Testing to see if the Report should print Per Week: Year information displayed on w i t h a l l c o r r e c t

page #62

= “2009”, week the report is correct to information displayed number = “1”

the data in the table weeks contained in the database

27 Generate reports form

Individual Week Testing to see if the Report should print Wages: Name information displayed on w i t h a l l c o r r e c t

page #62

= “Mark”, Year the report is correct to information displayed = 2009

the data in the table weeks contained in the database

37

Jacob Coy

3.3 Test Results: Test 1: Test 1 requires me to type the correct password for the application, this is passwordtest. In the screenshots below it is displayed as “************” because it uses an input mask. Once the password is typed in I clicked “login into the system!”

After clicking the login button you are taken to the main springboard form meaning that the password was correct and the login screen works when a correct password is entered as shown below.

38

Jacob Coy

Test 2: For test 2 I have to do the opposite of test 1 and type in an incorrect password in this example I will be using the word “suck” once typed into the password text box it is shown as “****”

39

Jacob Coy

After clicking the login button you are greeted with a message box that tells you what you have done wrong, and gives you the option to type the password again by clearing the text box once you have clicked ok.

40

Jacob Coy

Test 3: By repeating the proccess of typing in “suck” 5 times I am trying to see if the program will close itself after 5 failed attempts typing in suck as shown above again I am greeted with 5 message box’s saying “sorry that was an incorrect password” before the message box below. Once I had clicked ok the program closed.

Test 4: Opening the different functional forms from the main springboard form is a vital role of the program and as such testing that each form opens when the button is clicked is an important test as you would not be able to use the program without them. Clicking the “Add And Edit

41

Jacob Coy

Staff” button produces the following effect. This shows that the form I want opens

Test 5: Repeated steps as above for “Create A New Timesheet” produces. Proving form opens when button is clicked

42

Jacob Coy

Test 6: Repeated Steps as of test 4 for “View A Member Of Staff’s Week” produces. Proving form opens when button is clicked

43

Jacob Coy

Test 7: Repeated Steps as of test 4 for “Generate Report On A Member Of Staff” produces. Proving form opens when button is clicked

44

Jacob Coy

Test 8: Test 8 requires me to load the Add and Edit Staff form and find Staff ID 1. This is Betty, the test is to see if the data being displayed on the application is the same as in the staff table of the database to check this I will open the database and take a screenshot of the staff table where betty is. Putting this data alongside the applications will show whether it is identical. Data shown in the application:

Data shown in the database:

Data is displaying properly in the application with exactly the same data as the database.

45

Jacob Coy

Test 9: Test 9 is about making sure that I can add staff to the database into the staff table. To do so I clicked the + icon and added the data as shown in the test table, then hit save. To check that it has saved into the database I have opened it and taken screenshots from the staff table where john lies.

46

Jacob Coy

All the data is exactly the same in the database as was entered in the application. Test 10: For test 10 I need to navigate to staff member John and delete him, using the control panel at the top of the form I can navigate to john and simply just hit the X to delete him. By checking whether he has been deleted from the database will proove that this test is a success. Navigating to the John:

47

Jacob Coy

Deleting John using the application:

This is showing how John doesn’t exist in the database anymore after deletion using the form above.

48

Jacob Coy

Test 11: Starting on a new form test 11 involves the creation of a new timesheet with the values 2007 for the year and week number 50. The screenshot below shows the selection of these from the drop down menu’s then by clicking create it will create a timesheet for each member of staff for that year this can be proven by going into the database.

49

Jacob Coy

Test 12: Test 12 is to check that the algorithm I created to create a preset amount of years to choose from in drop down menu’s works. This is important as I have used them in every form and need to make sure that the algorithm works and so that data is valid and useful.

“Dim

year As Integer Dim i As Integer Dim DateValue As DateTime = Now

year = DateAndTime.Year(DateValue)

year = year - 3 For i = year To year + 7 cmbyear.Items.Add(i)

”

shown in this screenshot is the algorithm working the current year is 2009 taking -3 off leaves me at 2006 which is correct and adding 4 on takes me to 2013 which is also correct.

50

Jacob Coy

Part 2 of the test is to make sure that all 52 weeks show in the combo box as anymore than 52 or any less and members of staff may get confused.

Test 13: Test 13 is to make sure I cannot enter text into a numerical field and so that I cannot type in manually a week number that does not exist. I did this by opening the create timesheets form and immediately trying to type 54 into week number, doing so produces..

As it is coded trying to type 54 will produce 5 and 40 in the week number combo box this is because it is instructed to go to the closest value to what was entered by the keyboard by the items entered into the combobox’s properties. In terms of year it is physically impossible to type an “A” as you have to enter a number by typing it in similar to that of weeknumber or by selecting it from the drop down menu.

51

Jacob Coy

Test 14: Test 14 involves making sure that you cannot proceed to create a timesheet without supplying both a year and a week number as this would crash the application. In order to test this I have selected to enter just a week number of 1 and then click create. This produces.

52

Jacob Coy

Then reversing the idea and choosing only a year of 2009

As you can see you are not able to continue with creating a timesheet until you add a week number or a year.

53

Jacob Coy

Test 15: Test 15 involves making sure it is not possible to produce a timesheet in the database that already exists as this could a) wipe data already existing under that date. And b) could muck up payments with staff. Choosing a week and year that I knew were already existing in the database I clicked create and the results are shown below...

Test 16: Test 16 is to make sure that comboboxes for the names of the inmates in the prison were correct. Testing this out on the editweeks form I found that the inmates names and years were correct in the drop down boxes.

54

Jacob Coy

Test 17: In order to not corrupt the data in the database I need to make sure that it isnt not possible to open a timesheet and save the data for a year and week that doesn’t have a timesheet associated with it yet. When I choose Betty and 2006 as the year with week number set as 50 I am given I am greeted with this message box..

55

Jacob Coy

Test 18: Test 18 is to make sure that data that is pulled into the edit weeks form is correct with that of the database, so as to make sure that no accounting errors are made with a particular member of staff. Mark’s Details for week 1 of 2009 (in the application):

56

Jacob Coy

Mark’s details for week 1 of 2009 (in the database):

As proven the data being pulled is exactly the same as that displaying on the form.

Test 19: Making sure that you can actually save the data that you have just spent time typing into the form is important as it is not only stressful and frustrating to the end user but it also means that members of staff at boots are more productive with their time free to do other things. Making sure that the data is correct in the database also helps as well. To test this I will be editing a small piece of data on Mark’s week one details, his Monday will change to 3 hours instead of 2. Hitting the save button should save the details and opening the weeks database table should show that Monday is now showing as 3 instead of 2.

57

Jacob Coy

Data on Monday changed to 3.00 in the form:

Data in database for Monday of week 1 is shown as 3:

58

Jacob Coy

Test 20: It may be that you want to clear out all the records for a particular week and start again without having to delete manually each individual text box, as such the clear form button is there to ease this problem by wiping all the text boxes in one click. This test is to prove that the form can be cleared using the button. Charlie 2009 Week 2: data filled in.

59

Jacob Coy

Charlie 2009 week 2 all data cleared from text boxes after clicking the clear form button:

60

Jacob Coy

Test 21: Test 21 is a very important one as it means that you are not able to save a week for a member of staff if not all of the text boxes have data contained within them. This is a simple presence check validation rule and should produce a message box.

Without all of the text boxes filled in you cannot continue to save until you have entered a data value for all of them. This is useful as it will stop problems with half finished weekly timesheets.

61

Jacob Coy

Test 22: Making sure I can’t generate a report with half the amount of data need is important as it will mean that reports wont be printed that are incorrect or useless.

These show the message box that appears telling you, you need to choose a value for every field on the report section.

Test 23: Same as above but for next report...

62

Jacob Coy

Test 24: Same as previous two but for next report

Test 25: Choosing the values from the drop down boxes selecting Betty and 2009 as the year with week number 1 I hit generate report and shown below is what will come out of the printer when used by the end user.

63

Jacob Coy

Test 26: Choosing the values 2009 and week number one a report is generated for all the hours worked in the week 1 of 2009 with total calculated also. The form is shown below

checking the calculations on grand total of hours worked shows that they are all adding up correctly.

Test 27: choosing the values Mark and 2009 a report is generated showing all the weeks mark has worked in that year and how many hours he has worked in each week. A grand total of wages for the year for the week and sundays is also shown.

64

Jacob Coy

65

Jacob Coy

I have used a calculator to make sure that the grand totals are correct especially for the payments as shown in the paper tape below...

Failed Tests: Initially test 21’s validation did not work causing the program to crash. Going back into the code I realized that I had the name of one of the text boxes duplicated and I had forgot to change it, after changing the name to the correct one the validation worked perfectly.

66

Jacob Coy

4. Systems Maintenance 4.1 System Overview: The database itself was created to increase productivity in the store and create a less stressful working atmosphere as well as a replacement to the pen and paper system of the timesheets. The application itself will be stored on the main computer for the managerial team of the store, this way only the required members of staff will be able to log into their user accounts to access the application as well as knowing the password. The database itself was created in Access 2007 with the program itself being created in Visual Basic 2005. Mixing a variety of techniques with existing skills meant I was able to integrate existing access data and manipulate it using a bespoke piece of software. Most of the code was manually written as it pulled data from the database itself. A more detailed breakdown of some of the forms and their psuedo code can be found in the design section (all real functional code and screenshots of forms is avaliable.....)

4.1.2 Tables: Shown below are screenshots of all of my tables in the database as well as my entity relationship diagram. Table Staff: The staff table contains all the information about staff as well as their usual working hours for each day of the week as well as their normal pay.

67

Jacob Coy

Table: Weeks This table contains all the information that is required to see how many hours each member of staff works for a particular week in a year.

68

Jacob Coy

69

Jacob Coy

4.2 Forms: Password Login form:

Main Springboard

On click event opens add edit staff form

On click event opens create a new timesheet form

On click event opens a member of staffs week On click event opens create reports form

70

Jacob Coy

Add and edit staff:

Bound form pulling all data from the staff table

This form displays all the members of staff in the database and allows you to add staff and then delete staff Create a timesheet form:

Year is generated from the algorithm documented above. And there are 52 weeks in the week number box that was manually entered. On click event create button runs through the

database looking for existing weeks

and years and then creating new ones if they don’t already exist.

71

Jacob Coy

Edit a member of staffs week form:

This form pulls all the data from the database. Using criteria chosen from the combo boxes.

On click event saves all the data into the respective areas of the database.

On click event clears all the text boxes on the form.

72

Jacob Coy

Generate Reports Form:

On click event runs through the database. And pulls the data it needs to generate reports.

73

Jacob Coy

4.3 Reports Working hours report (design view)

total wages for certain weeks (design view)

74

Jacob Coy

Total staff wages report (design view)

Procedures created: Private Sub form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim year As Integer Dim i As Integer Dim DateValue As DateTime = Now year = DateAndTime.Year(DateValue) year = year - 3 For i = year To year + 7 cmbyear.Items.Add(i) Next End Sub End Class This algorithm is for creating the date required in the drop down boxes for year scattered across the application. By taking the date from the current year from the computers clock it goes 3 backwards and 4 ahead, this is loaded in the background, outputting the dates required in the comboboxes.

75

Jacob Coy

5. User Manual:

76

Jacob Coy

6. Appraisal 6.1 Comparison to objectives: My system was required to do the following things as detailed earlier...

•A startup home screen with a clear function of allowing the user to navigate to the different functions of the application simply.

• A form containing all the necessary elements to modify and add members of staff and their details simply and efficiently

• Form to edit an individuals week and the hours they have worked using numbers instead of using 15 minute blocks

• The ability to create reports for a particular member of staff cross referenced by the year month week and day Objective 1: I believe that I have managed to fulfill the criteria for objective 1 as I feel that the springboard form that I created is simple and easy to navigate. This means that staff required to use the program won’t need specialist IT training as I feel personally that it is a very easy program to navigate from and you also can’t go wrong with opening too many things at once as it is only possible to open 1 window at a time. Objective 2: Objective 2 required me to create a form that was complicated enough to add and edit staff whilst maintaining a sense of simplicity to allow for the staff to be able to use it without a huge amount of training. I believe that I have achieved this very well as it is a simple bound form that allows for the deletion, addition and editing of staff easily due to its control bar which is similar to other functions staff may have seen in other windows applications. If I had more time I would have liked to create a custom form for this instead of using a bound form to do most of the work for me with my own skin over the top.

77

Jacob Coy

Objective 3: Objective 3 was achieved through the use of my edit weeks form. I believe that I have managed to fulfill the needs for the system to remain functional although I have always had the feeling that something just isn’t right with this form. It works perfectly fine although I do feel that it could still have been improved further with possible a simpler user interface or something that doesn’t make this form look so daunting. Objective 4: I believe that the reports I have created fulfill the needs of the end user and match this objective. They are simple to follow and create whilst still having a complex system behind all the buttons and combo boxes. I believe that I could have created more boots styled forms if I had more time though.

6.2 Further Development: Overall I believe that the system I have created for boots has been very successful in sorting out a few problems in the store and I believe that it will increase productivity

System security - I believe that the system security is effective to an extent although I



would have liked to extend this form further to allow for a change of passwords using



additional elements or using a form in the database.



Edit Weeks Form - This form performs well and to my expectations although if I had



more time I would have preferred to have improved the design over this and added



extra validation as through extensive testing it does sometimes crash after a lot of use.



Backing Up The Data - The system itself is pretty secure and safe although it is not



protected against problems that are outside of what code can do. For example power



cuts etc. As a result of events such as that the system may lose data or data may not



be saved so I would have preffered to implement a backing up system, preferably one



that backs up all data to a secure FTP location away from the store.

Reports - Although the reports fufil the functions that they require, they just don’t match

the style of the application itself if I had more time then I would have liked to create a

bespoke report design that was in line with the application itself featuring boots logo’s.

In general though I think that I have created the best program for the job and I have done the best that I could with the time I had available to me and using my skill sets. Of course they will always be room for improvement but I think I have achieved what was required of me.

78

Jacob Coy

6.3 User Feedback: Taking my program to my end users Boots store 1019 Romsey and giving them a quick how to on the program showing them how to edit staff and edit the weeks. I gave the reigns over to the two members of staff that would be using the program themselves. They took to the use of the program very quickly and managed to navigate to the sections of the program that they wanted easily and perform basic functions. Giving them a copy of the user guide I gave them 30 minutes to get used to how to perform the further more complicated functions and then let them back on the program. They didn’t struggle to perform these tasks creating reports for members of staff based upon the criteria that they wanted. With this in mind we started to add timesheets for the current week and edit the data in them. The store was very impressed with the system and they felt that it would be an overall improvement to all of the paper in the shop. The two primary users were also very impressed saying it was simple to use yet functional and that it would fit into the general everyday working cycle of the store.

79

Jacob Coy

7.0 System Code Login Form: Public Class login Dim passwordprotector As Integer Private Sub btneditstaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btneditstaff.Click

If txtboxlogin.Text = "passwordtest" Then

form1.Show() Me.Hide() 'Me.Enabled = False Else MsgBox("Sorry That Was An Incorrect Password") txtboxlogin.Clear() passwordprotector = passwordprotector + 1 If passwordprotector = 5 Then MsgBox("You Have Entered The Password Incorrectly 5 Times Application Is

Closing")

Me.Close() End If End If End Sub End Class

80

Jacob Coy

Springboard Form: Public Class form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load StaffTableAdapter1.Fill(BootsdbDataSet1.staff) WeeksTableAdapter1.Fill(BootsdbDataSet1.Weeks) QuerytotalwagesTableAdapter1.Fill(BootsdbDataSet1.querytotalwages) End Sub Private Sub btneditstaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btneditstaff.Click frmaddedit.ShowDialog() End Sub

Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click frmeditweeks.ShowDialog() End Sub

Private Sub btntimesheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntimesheet.Click frmtimesheets.ShowDialog() End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click frmrecords.ShowDialog() End Sub End Class

81

Jacob Coy

Add and Edit Staff Form: Public Class form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load StaffTableAdapter1.Fill(BootsdbDataSet1.staff) WeeksTableAdapter1.Fill(BootsdbDataSet1.Weeks) QuerytotalwagesTableAdapter1.Fill(BootsdbDataSet1.querytotalwages) End Sub Private Sub btneditstaff_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btneditstaff.Click frmaddedit.ShowDialog() End Sub

Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click frmeditweeks.ShowDialog() End Sub

Private Sub btntimesheet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntimesheet.Click frmtimesheets.ShowDialog() End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click frmrecords.ShowDialog() End Sub End Class

82

Jacob Coy

Create A New Timesheet Form: Public Class frmtimesheets

Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click Dim i As Integer Dim formatCorrect As Boolean If cmbyear.Text = "" Then MsgBox("you have not selected a year") Exit Sub End If If cmbweek.Text = "" Then MsgBox("you have not entered or selected a week number") Exit Sub End If For i = 0 To form1.BootsdbDataSet1.staff.Count - 1 ' Dim arow As marksdbDataSet.smarkRow = form1.MarksdbDataSet1.smark.NewsmarkRow Dim arow As bootsdbDataSet.WeeksRow = form1.BootsdbDataSet1.Weeks.NewWeeksRow arow.Staff_ID = form1.BootsdbDataSet1.staff(i).Staff_ID arow.Week_Number = cmbweek.Text arow.Year = cmbyear.Text arow.Monday = 0 arow.monday_start = TimeOfDay arow.Monday_Finish = TimeOfDay arow.Tuesday = 0 arow.Tuesday_start = TimeOfDay arow.Tuesday_Finish = TimeOfDay arow.Wednesday = 0 arow.Wednesday_start = TimeOfDay arow.Wednesday_Finish = TimeOfDay arow.Thursday = 0 arow.Thursday_start = TimeOfDay arow.Thursday_Finish = TimeOfDay arow.Friday = 0 arow.Friday_start = TimeOfDay arow.Friday_Finish = TimeOfDay arow.Saturday = 0 arow.Saturday_start = TimeOfDay arow.Saturday_Finish = TimeOfDay arow.Sunday = 0 arow.Sunday_start = TimeOfDay arow.Sunday_Finish = TimeOfDay

83

Jacob Coy arow.Standard_Payment = 0 arow.Overtime_Payment = 0 If cmbyear.Text = "" Then formatCorrect = False MsgBox("you have not selected a year") End If

Try form1.BootsdbDataSet1.Weeks.Rows.Add(arow) form1.WeeksTableAdapter1.Update(form1.BootsdbDataSet1.Weeks) Catch ex As Exception MsgBox("Year & Week Already Exists In The Database") Exit Sub End Try Next cmbweek.SelectedText = "" cmbyear.SelectedText = "" End Sub

Private Sub frmtimesheets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim year As Integer Dim i As Integer Dim DateValue As DateTime = Now

year = DateAndTime.Year(DateValue)

year = year - 3 For i = year To year + 7 cmbyear.Items.Add(i)

Next End Sub

Private Sub cmbyear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbyear.SelectedIndexChanged

End Sub End Class

84

Jacob Coy

Edit Weeks Form: Public Class frmeditweeks

Private Sub frmeditweeks_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'BootsdbDataSet.staff' table. You can move, or remove it, as needed. Me.StaffTableAdapter.Fill(Me.BootsdbDataSet.staff) Dim year As Integer Dim i As Integer Dim counter, selIndex As Integer Dim DateValue As DateTime = Now year = DateAndTime.Year(DateValue) year = year - 3 For i = year To year + 7 If i = DateAndTime.Year(DateValue) Then selIndex = counter End If counter = counter + 1 cmbyear.Items.Add(i) Next

cmbyear.SelectedIndex = selIndex End Sub Private Sub btngen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngen.Click Dim weekNumber As Integer Dim staffid As Integer Dim i, theyear As Integer Dim found As Boolean = False Try staffid = cmbstaffid.SelectedValue theyear = cmbyear.Text weekNumber = cmbweek.Text Catch ex As Exception MsgBox("you havent selected anything") Exit Sub

End Try found = False For i = 0 To form1.BootsdbDataSet1.Weeks.Count - 1 If (staffid = form1.BootsdbDataSet1.Weeks(i).Staff_ID) And (form1.BootsdbDataSet1.Weeks(i).Week_Number = weekNumber) And (form1.BootsdbDataSet1.Weeks(i).Year = theyear) Then

85

Jacob Coy found = True 'txtyear.Text = form1.BootsdbDataSet1.Weeks(i).Year txtmonday.Text = form1.BootsdbDataSet1.Weeks(i).Monday txtmondaystart.Text = form1.BootsdbDataSet1.Weeks(i).monday_start txtmondayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Monday_Finish txttuesday.Text = form1.BootsdbDataSet1.Weeks(i).Tuesday txttuesdaystart.Text = form1.BootsdbDataSet1.Weeks(i).Tuesday_start txttuesdayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Tuesday_Finish txtwednesday.Text = form1.BootsdbDataSet1.Weeks(i).Wednesday txtwednesdaystart.Text = form1.BootsdbDataSet1.Weeks(i).Wednesday_start txtwednesdayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Wednesday_Finish txtthursday.Text = form1.BootsdbDataSet1.Weeks(i).Thursday txtthursdaystart.Text = form1.BootsdbDataSet1.Weeks(i).Thursday_start txtthursdayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Thursday_Finish txtFriday.Text = form1.BootsdbDataSet1.Weeks(i).Friday txtfridaystart.Text = form1.BootsdbDataSet1.Weeks(i).Friday_start txtfridayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Friday_Finish txtsaturday.Text = form1.BootsdbDataSet1.Weeks(i).Saturday txtsaturdaystart.Text = form1.BootsdbDataSet1.Weeks(i).Saturday_start txtsaturdayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Saturday_Finish txtsunday.Text = form1.BootsdbDataSet1.Weeks(i).Sunday txtsundaystart.Text = form1.BootsdbDataSet1.Weeks(i).Sunday_start txtsundayfinish.Text = form1.BootsdbDataSet1.Weeks(i).Sunday_Finish txtstandard.Text = form1.BootsdbDataSet1.Weeks(i).Standard_Payment txtovertime.Text = form1.BootsdbDataSet1.Weeks(i).Overtime_Payment End If Next If found = False Then MsgBox("this week hasnt been created yet in the database!") Call btnclearform_Click(sender, e) End If

End Sub

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click Dim weeksrow As bootsdbDataSet.WeeksRow = form1.BootsdbDataSet1.Weeks.NewWeeksRow Dim year, weeknumber, Staffid As Integer If txtmonday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub

86

Jacob Coy End If

If txtmondaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtmondayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txttuesday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txttuesdaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txttuesdayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtwednesday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtwednesdaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtwednesdayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtthursday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtthursdaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If

87

Jacob Coy If txtthursdayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtFriday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtfridaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtfridayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsaturday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsaturdaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsaturdayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsunday.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsundaystart.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtsundayfinish.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If txtstandard.Text = "" Then MsgBox("you have not entered a value for every field on this form!")

88

Jacob Coy Exit Sub End If If txtovertime.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If year = cmbyear.Text weeknumber = cmbweek.Text Staffid = cmbstaffid.SelectedValue weeksrow = form1.BootsdbDataSet1.Weeks.FindByWeek_NumberYearStaff_ID(weeknumber, year, Staffid) weeksrow.Monday = txtmonday.Text weeksrow.monday_start = txtmondaystart.Text weeksrow.Monday_Finish = txtmondayfinish.Text weeksrow.Tuesday = txttuesday.Text weeksrow.Tuesday_start = txttuesdaystart.Text weeksrow.Tuesday_Finish = txttuesdayfinish.Text weeksrow.Wednesday = txtwednesday.Text weeksrow.Wednesday_start = txtwednesdaystart.Text weeksrow.Wednesday_Finish = txtwednesdayfinish.Text weeksrow.Thursday = txtthursday.Text weeksrow.Thursday_start = txtthursdaystart.Text weeksrow.Thursday_Finish = txtthursdayfinish.Text weeksrow.Friday = txtFriday.Text weeksrow.Friday_start = txtfridaystart.Text weeksrow.Friday_Finish = txtfridayfinish.Text weeksrow.Saturday = txtsaturday.Text weeksrow.Saturday_start = txtsaturdaystart.Text weeksrow.Saturday_Finish = txtsaturdayfinish.Text weeksrow.Sunday = txtsunday.Text weeksrow.Sunday_start = txtsundaystart.Text weeksrow.Sunday_Finish = txtsundayfinish.Text weeksrow.Standard_Payment = txtstandard.Text weeksrow.Overtime_Payment = txtovertime.Text form1.WeeksTableAdapter1.Update(weeksrow)

End Sub Private Sub btnclearform_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclearform.Click 'txtyear.Clear() txtmonday.Clear() txtmondaystart.Clear() txtmondayfinish.Clear()

89

Jacob Coy txttuesday.Clear() txttuesdaystart.Clear() txttuesdayfinish.Clear() txtwednesday.Clear() txtwednesdaystart.Clear() txtwednesdayfinish.Clear() txtthursday.Clear() txtthursdaystart.Clear() txtthursdayfinish.Clear() txtFriday.Clear() txtfridaystart.Clear() txtfridayfinish.Clear() txtsaturday.Clear() txtsaturdaystart.Clear() txtsaturdayfinish.Clear() txtsunday.Clear() txtsundaystart.Clear() txtsundayfinish.Clear() txtstandard.Clear() txtovertime.Clear() End Sub End Class

90

Jacob Coy

Generate Reports Form: Public Class frmrecords

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngenstaffpay.Click Dim accessapp As Object If cmbyear3.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If cmbname2.Text = "" Then MsgBox("You have not entered a value for every field on this form!") Exit Sub End If accessapp = CreateObject("access.application") accessapp.opencurrentdatabase(System.Windows.Forms.Application.StartupPath & "\bootsdb.mdb") accessapp.docmd.openreport("individual weeks", 0, , "[Year] =" & cmbyear3.Text & " AND [staff ID] =" & cmbname2.SelectedValue) accessapp.quit() accessapp = Nothing End Sub

Private Sub btngenstaffhours_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngenstaffhours.Click

Dim accessapp As Object If cmbyear.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If cmbweeknumber.Text = "" Then MsgBox("You have not entered a value for every field on this form!") Exit Sub End If If cmbname.Text = "" Then MsgBox("You have not entered a value for every field on this form!") Exit Sub End If accessapp = CreateObject("access.application") accessapp.opencurrentdatabase(System.Windows.Forms.Application.StartupPath & "\bootsdb.mdb") & "'")

accessapp.docmd.openreport("genstaffhoursreport", 0, , "[Name] ='" & cmbname.Text

91

Jacob Coy accessapp.quit() accessapp = Nothing

End Sub

Private Sub btngentotalwagepayments_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngentotalwagepayments.Click Dim accessapp As Object If cmbyear2.Text = "" Then MsgBox("you have not entered a value for every field on this form!") Exit Sub End If If cmbweek2.Text = "" Then MsgBox("You have not entered a value for every field on this form!") Exit Sub End If accessapp = CreateObject("access.application") accessapp.opencurrentdatabase(System.Windows.Forms.Application.StartupPath & "\bootsdb.mdb") accessapp.docmd.openreport("total staff wages report", 0, , "[week number] =" & cmbweek2.Text & " AND [year] = " & cmbyear2.Text) accessapp.quit() accessapp = Nothing

End Sub

Private Sub frmrecords_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'BootsdbDataSet.Weeks' table. You can move, or remove it, as needed. Me.WeeksTableAdapter.Fill(Me.BootsdbDataSet.Weeks) 'TODO: This line of code loads data into the 'BootsdbDataSet.staff' table. You can move, or remove it, as needed. Me.StaffTableAdapter.Fill(Me.BootsdbDataSet.staff)

End Sub Private Sub form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim year As Integer Dim i As Integer Dim DateValue As DateTime = Now

year = DateAndTime.Year(DateValue)

92

Jacob Coy year = year - 3 For i = year To year + 7

cmbyear.Items.Add(i) cmbyear2.Items.Add(i) cmbyear3.Items.Add(i) Next End Sub End Class

93