Home

Example Application: A Time Sheet

   

Introduction

A time sheet is a dialog box or a form on which employees record the time they have worked. Most companies use a two-week schedule. In most cases, an employee records the time one week at a time.

If you create an application for a time sheet, of course there are various ways you can implement it, depending on what the company or its accounting department wants. For our simple example, we will first let a user enter his employee number and the starting date of the time sheet. Based on these two pieces of information, we will check whether the employee had previously entered or started a time sheet for that time frame. If thatís the case, we will open that time sheet and display its record. If there is no time recorded for the employee in that time frame, we will let the user create a new time sheet.

The Employees

Obviously the primary users of a time sheet are the employees. A company may need a lot of information from employees but in most cases, an employee number and the name of the employee are enough.

Practical LearningPractical Learning: Introducing a Time Sheet

  1. Start Microsoft Access
  2. In the File Name section, change the database name to TimeSheetCalculations
  3. Click Create
  4. On the Ribbon, click Create
  5. In the Tables section, click Table Design
  6. Create the columns as follows:
     
    Field Name Data Type Field Size Format Caption
    EmployeeNumber Short Text 10   Employee #
    FirstName Short Text 25   First Name
    LastName Short Text 25   Last Name
    HourlySalary Number Double Fixed Hourly Salary
  7. In the top section, right-click EmployeeNumber and click Primary Key
  8. Close the table
  9. When asked whether you want to save, click Yes
  10. Set the name to Employees and click OK

Creating a Time Sheet

As far as a database is concerned, a time sheet is a list that contains some information about the employees and the time they workd. In most cases, the basic information about an employee is just what is enough to identify the employee. This should be a piece of informatiion that uniquely identify each emmployee. For most businesses, this is done through an employee number. The time worked is registered for each day from 0 to 24. Most companies also allow fractions of an hour, usually as half an hour. Some (rare) companies also consider time in fractions of quarters (.25,.50, and .75).

After an employee has filled her time sheet, she can click a button such as Submit, OK, or Apply. In Microsoft Access, a simple way to create a time record would consist of calling the AddNew() method of a recordset, assigning the desired values to the fields on the table, and closing this by calling the Update() method of the recordset. Here is an example:

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmitClick_Error

    Dim dbFunDS As Database
    Dim rsTimeSheets As Recordset
    
    If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
        Exit Sub
    End If
    
    If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
        Exit Sub
    End If
        
    Set dbFunDS = CurrentDb
    Set rsTimeSheets = dbFunDS.OpenRecordset("TimeSheets1")
        
    With rsTimeSheets
        .AddNew
        .Fields("EmployeeNumber").Value = txtEmployeeNumber
        .Fields("StartDate").Value = txtStartDate
        .Fields("Week1Monday").Value = txtWeek1Monday
        .Fields("Week1Tuesday").Value = txtWeek1Tuesday
        .Fields("Week1Wednesday").Value = txtWeek1Wednesday
        .Fields("Week1Thursday").Value = txtWeek1Thursday
        .Fields("Week1Friday").Value = txtWeek1Friday
        .Fields("Week1Saturday").Value = txtWeek1Saturday
        .Fields("Week1Sunday").Value = txtWeek1Sunday
        .Fields("Week2Monday").Value = txtWeek2Monday
        .Fields("Week2Tuesday").Value = txtWeek2Tuesday
        .Fields("Week2Wednesday").Value = txtWeek2Wednesday
        .Fields("Week2Thursday").Value = txtWeek2Thursday
        .Fields("Week2Friday").Value = txtWeek2Friday
        .Fields("Week2Saturday").Value = txtWeek2Saturday
        .Fields("Week2Sunday").Value = txtWeek2Sunday
        .Update
    
    End With
            
    Set rsTimeSheets = Nothing
    Set dbFunDS = Nothing
    
    Exit Sub
    
cmdSubmitClick_Error:
    If Err.Number = 3021 Then
        MsgBox "Invalid operation: A problem occurred when trying to submit the time sheet." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               vbOKOnly Or vbInformation, "Fun Department Store"
        txtEmployeeName = ""
        Exit Sub
    Else
        
    End If
    
    Resume Next
End Sub

This code is fine if the employee is only creating a new time sheet. Most companies require that each employee enter the time worked at the end of the day, and most of the time, the employee registers the time worked for one day at a time, or for only that day. To address this issue, after an employee has entered his employee number and the start date of the time sheet, we will check if the employee had previously entered time for at least one day based on the employee number and the start date. If we find such a record, we will open that time sheet and let the user update it. If we don't find a time sheet with that information, we will consider that the employee has filling a new time sheet.

Practical LearningPractical Learning: Introducing a Time Sheet

  1. On the Ribbon, click Create
  2. In the Forms section, click Form Design
  3. Design a form as follows:
     
    Fun Department Store - New Time Sheet
    Control Text Box Name Caption
    Text Box Text Box EmployeeNumber Employee #:
    Text Box Text Box txtEmployeeName  
    Text Box Text Box txtStartDate Start Date:
    Text Box Text Box txtEndDate End Date:
    Label Text Box   Monday
    Label Text Box   Tuesday
    Label Text Box   Wednesday
    Label Text Box   Thursday
    Label Text Box   Friday
    Label Text Box   Saturday
    Label Text Box   Sunday
    Text Box Text Box txtWeek1Monday Week 1:
    Text Box Text Box txtWeek1Tuesday  
    Text Box Text Box txtWeek1Wednesday  
    Text Box Text Box txtWeek1Thursday  
    Text Box Text Box txtWeek1Friday  
    Text Box Text Box txtWeek1Saturday  
    Text Box Text Box txtWeek1Sunday  
    Text Box Text Box txtWeek2Monday Week 2:
    Text Box Text Box txtWeek2Tuesday  
    Text Box Text Box txtWeek2Wednesday  
    Text Box Text Box txtWeek2Thursday  
    Text Box Text Box txtWeek2Friday  
    Text Box Text Box txtWeek2Saturday  
    Text Box Text Box txtWeek2Sunday  
    Button Button cmdSubmit Submit
    Button Button cmdClose Close
  4. Save the form as NewTimeSheet
  5. Right-click the Submit button and click Build Event...
  6. In the Choose Builder dialog box, double-click Code Builder
  7. Set the code in the module as follows:
    Option Compare Database
    Option Explicit
    
    Private Sub ResetForm()
        txtTimeSheetID.Visible = False
        txtEmployeeNumber = ""
        txtEmployeeName = ""
        txtStartDate = ""
        txtEndDate = ""
        txtWeek1Monday = "0.00"
        txtWeek1Tuesday = "0.00"
        txtWeek1Wednesday = "0.00"
        txtWeek1Thursday = "0.00"
        txtWeek1Friday = "0.00"
        txtWeek1Saturday = "0.00"
        txtWeek1Sunday = "0.00"
        txtWeek2Monday = "0.00"
        txtWeek2Tuesday = "0.00"
        txtWeek2Wednesday = "0.00"
        txtWeek2Thursday = "0.00"
        txtWeek2Friday = "0.00"
        txtWeek2Saturday = "0.00"
        txtWeek2Sunday = "0.00"
    End Sub
    
    Private Sub Form_Load()
        ResetForm
    End Sub
    
    Private Sub txtEmployeeNumber_LostFocus()
    On Error GoTo txtEmployeeNumber_Error
    
        Dim dbFunDS As Database
        Dim rsEmployees As Recordset
        
        Dim EmployeeFound As Boolean
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Or (txtEmployeeNumber = "") Then
            txtEmployeeName = ""
            Exit Sub
        Else
            Set dbFunDS = CurrentDb
            Set rsEmployees = dbFunDS.OpenRecordset("SELECT FirstName, LastName " & _
                                                    "FROM Employees " & _
                                                    "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';")
        
            If rsEmployees.RecordCount > 0 Then
                EmployeeFound = True
                txtEmployeeName = rsEmployees("LastName") & ", " & rsEmployees("FirstName")
            End If
        
            If EmployeeFound = False Then
                MsgBox "There is no staff member with that employee number.", _
                	   vbOKOnly Or vbInformation, _
                	   "Fun Department Store - Employee Time Sheet"
            End If
        
            Set dbFunDS = Nothing
            Set rsEmployees = Nothing
        End If
        
        Exit Sub
        
    txtEmployeeNumber_Error:
        If Err.Number = 3021 Then
            MsgBox "Invalid Employee Number: The employee number you entered was not found in the database.", _
                   vbOKOnly Or vbInformation, "Fun Department Store - Employee Time Sheet"
               Exit Sub
        Else
            MsgBox "A problem occurred when trying to retrieve the employee record." & vbCrLf & _
                   "Error #:     " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, _
                   vbOKOnly Or vbInformation, "Fun Department Store - Employee Time Sheet"
        End If
        
        Resume Next
    End Sub
    
    Private Sub txtStartDate_LostFocus()
    On Error GoTo txtStartDate_Error
        
        Dim dbFunDS As Database
        Dim rsTimeSheets As Recordset
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            Exit Sub
        ElseIf IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        Else
            txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
            
            Set dbFunDS = CurrentDb
            
            ' get the values in the TimeSheet table
            Set rsTimeSheets = dbFunDS.OpenRecordset("SELECT TimeSheetID, EmployeeNumber, StartDate, " & _
                                                     "Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, " & _
                                                     "Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday " & _
                                                     "FROM TimeSheets1 " & _
                                                     "WHERE EmployeeNumber = '" & txtEmployeeNumber & "' AND StartDate = #" & txtStartDate & "#;")
            
            ' To get the ending date of hte time frame, add 2 weeks to the specified starting date
            txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
            
            ' Find out if there was a record in the TimeSheet table that corresponds to the specified employee number and the starting date
            If rsTimeSheets.RecordCount > 0 Then
                ' If there exists a time record for the employee
                ' number and the start date, display its values
                txtTimeSheetID.Visible = True
                txtTimeSheetID = rsTimeSheets("TimeSheetID")
                txtEmployeeNumber = rsTimeSheets("EmployeeNumber")
                txtStartDate = rsTimeSheets("StartDate")
                txtWeek1Monday = rsTimeSheets("Week1Monday")
                txtWeek1Tuesday = rsTimeSheets("Week1Tuesday")
                txtWeek1Wednesday = rsTimeSheets("Week1Wednesday")
                txtWeek1Thursday = rsTimeSheets("Week1Thursday")
                txtWeek1Friday = rsTimeSheets("Week1Friday")
                txtWeek1Saturday = rsTimeSheets("Week1Saturday")
                txtWeek1Sunday = rsTimeSheets("Week1Sunday")
                txtWeek2Monday = rsTimeSheets("Week2Monday")
                txtWeek2Tuesday = rsTimeSheets("Week2Tuesday")
                txtWeek2Wednesday = rsTimeSheets("Week2Wednesday")
                txtWeek2Thursday = rsTimeSheets("Week2Thursday")
                txtWeek2Friday = rsTimeSheets("Week2Friday")
                txtWeek2Saturday = rsTimeSheets("Week2Saturday")
                txtWeek2Sunday = rsTimeSheets("Week2Sunday")
            Else
                ' If no record was found for the employee number in the
                ' specified time frame, get ready to create a new record
                txtTimeSheetID.Visible = False
                
                txtWeek1Monday = "0.00"
                txtWeek1Tuesday = "0.00"
                txtWeek1Wednesday = "0.00"
                txtWeek1Thursday = "0.00"
                txtWeek1Friday = "0.00"
                txtWeek1Saturday = "0.00"
                txtWeek1Sunday = "0.00"
                txtWeek2Monday = "0.00"
                txtWeek2Tuesday = "0.00"
                txtWeek2Wednesday = "0.00"
                txtWeek2Thursday = "0.00"
                txtWeek2Friday = "0.00"
                txtWeek2Saturday = "0.00"
                txtWeek2Sunday = "0.00"
            End If
        End If
        
        Exit Sub
        
    txtStartDate_Error:
        If Err.Number = 3021 Then
            MsgBox "Invalid start date: The start date you specified is not correct.", _
                   vbOKOnly Or vbInformation, "Fun Department Store - Employee Time Sheet"
            txtEmployeeName = ""
            Exit Sub
        Else
            MsgBox "A problem occurred when trying to retrieve the time sheet." & vbCrLf & _
                   "Error #:     " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, _
                   vbOKOnly Or vbInformation, _
                   "Fun Department Store - Employee Time Sheet"
        End If
        
        Resume Next
    End Sub
    
    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmitClick_Error
    
        Dim dbFunDS As Database
        Dim rsTimeSheets As Recordset
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        End If
            
        Set dbFunDS = CurrentDb
        ' We need to find out whether the user is creating a new time record or he is updating an existing time sheet
        Set rsTimeSheets = dbFunDS.OpenRecordset("SELECT TimeSheetID, EmployeeNumber, StartDate, " & _
                                                 "Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, " & _
                                                 "Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday " & _
                                                 "FROM TimeSheets1 " & _
                                                 "WHERE EmployeeNumber = '" & txtEmployeeNumber & "' AND StartDate = #" & txtStartDate & "#;")
            
        With rsTimeSheets
            If .RecordCount > 0 Then
                ' If a record was found with the current employee number
                ' and the specified start date, the employee probably simply wants to update her time sheet
                .Edit
                .Fields("Week1Monday").Value = txtWeek1Monday
                .Fields("Week1Tuesday").Value = txtWeek1Tuesday
                .Fields("Week1Wednesday").Value = txtWeek1Wednesday
                .Fields("Week1Thursday").Value = txtWeek1Thursday
                .Fields("Week1Friday").Value = txtWeek1Friday
                .Fields("Week1Saturday").Value = txtWeek1Saturday
                .Fields("Week1Sunday").Value = txtWeek1Sunday
                .Fields("Week2Monday").Value = txtWeek2Monday
                .Fields("Week2Tuesday").Value = txtWeek2Tuesday
                .Fields("Week2Wednesday").Value = txtWeek2Wednesday
                .Fields("Week2Thursday").Value = txtWeek2Thursday
                .Fields("Week2Friday").Value = txtWeek2Friday
                .Fields("Week2Saturday").Value = txtWeek2Saturday
                .Fields("Week2Sunday").Value = txtWeek2Sunday
                .Update
            
                MsgBox "The time sheet has been updated." & vbCrLf & _
                       vbOKOnly Or vbInformation, "Fun Department Store"
            Else
                ' If no record was found with the current employee number
                ' and the specified start date, the employee probably wants to create a new time sheet
                .AddNew
                .Fields("EmployeeNumber").Value = txtEmployeeNumber
                .Fields("StartDate").Value = txtStartDate
                .Fields("Week1Monday").Value = txtWeek1Monday
                .Fields("Week1Tuesday").Value = txtWeek1Tuesday
                .Fields("Week1Wednesday").Value = txtWeek1Wednesday
                .Fields("Week1Thursday").Value = txtWeek1Thursday
                .Fields("Week1Friday").Value = txtWeek1Friday
                .Fields("Week1Saturday").Value = txtWeek1Saturday
                .Fields("Week1Sunday").Value = txtWeek1Sunday
                .Fields("Week2Monday").Value = txtWeek2Monday
                .Fields("Week2Tuesday").Value = txtWeek2Tuesday
                .Fields("Week2Wednesday").Value = txtWeek2Wednesday
                .Fields("Week2Thursday").Value = txtWeek2Thursday
                .Fields("Week2Friday").Value = txtWeek2Friday
                .Fields("Week2Saturday").Value = txtWeek2Saturday
                .Fields("Week2Sunday").Value = txtWeek2Sunday
                .Update
            
                MsgBox "The new time sheet has been created and saved." & vbCrLf & _
                       vbOKOnly Or vbInformation, "Fun Department Store"
            End If
        End With
            
        Set rsTimeSheets = Nothing
        Set dbFunDS = Nothing
        
        ResetForm
        Exit Sub
        
    cmdSubmitClick_Error:
        If Err.Number = 3021 Then
            MsgBox "Invalid operation: A problem occurred when trying to submit the time sheet." & vbCrLf & _
                   "Error #:     " & Err.Number & vbCrLf & _
                   vbOKOnly Or vbInformation, _
                   "Fun Department Store - Employee Time Sheet"
            ResetForm
            Exit Sub
        Else
            
        End If
        
        Resume Next
    End Sub
    
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    
    End Sub
    
    Private Sub txtStartDate_BeforeUpdate(Cancel As Integer)
    
    End Sub
  8. On the Ribbon, click Create
  9. In the Queries section, click Query Design
  10. On the Show Table dialog box, click Close
  11. Right-click the top section of the view and click SQL View
  12. Type the following code:
    CREATE TABLE TimeSheets
    (
        TimeSheetID Counter(1000, 1),
        EmployeeNumber Text(10) not null
    );
  13. To execute, in the Results section of the Design tab of the Ribbon, click Run
  14. Close the Query1 window
  15. When asked whether you want to save, click No
  16. In the Navigation Pane, right-click TimeSheets and click Design View
  17. Complete the design of the table as follows:
     
    Field Name Data Type Field Size Format Caption Default Value
    TimeSheetID No Change No Change   Time Sheet ID  
    EmployeeNumber Short Text 10 Employee #  
    StartDate Date/Time Long Date Start Date
    Week1Monday Number Double Fixed Week 1 Monday 0.00
    Week1Tuesday Number Double Fixed Week 1 Tuesday 0.00
    Week1Wednesday Number Double Fixed Week 1 Wednesday 0.00
    Week1Thursday Number Double Fixed Week 1 Thursday 0.00
    Week1Friday Number Double Fixed Week 1 Friday 0.00
    Week1Saturday Number Double Fixed Week 1 Saturday 0.00
    Week1Sunday Number Double Fixed Week 1 Sunday 0.00
    Week2Monday Number Double Fixed Week 2 Monday 0.00
    Week2Tuesday Number Double Fixed Week 2 Tuesday 0.00
    Week2Wednesday Number Double Fixed Week 2 Wednesday 0.00
    Week2Thursday Number Double Fixed Week 2 Thursday 0.00
    Week2Friday Number Double Fixed Week 2 Friday 0.00
    Week2Saturday Number Double Fixed Week 2 Saturday 0.00
    Week2Sunday Number Double Fixed Week 2 Sunday 0.00
  18. In the top section, right-click TimeSheetID and click Primary Key
  19. Close the table
  20. When asked whether you want to save, click Yes
  21. Set the name to TimeSheets and click OK

Overtime and the Weekly Time Sheet

The easiest way to calculate the values of a time sheet is to simply add the time worked for each day to get the total time worked. If an employee worked some limited time, this is fine. Most companies pay overtime if an employee works beyond a certain amount of time. There are different techniques to apply or to calculate overtime, but there are two broad categories.

The time worked can be considered on a weekly basis. In this case, an employee typically works 5 days a week for 40 hours. Any period over 40 hours is considered overtime.

Practical LearningPractical Learning: Evaluating a Payroll

  1. On the Ribbon, click Create
  2. In the Forms section, click Form Design
  3. Save the form as PayrollEvaluation1
  4. Design a form as follows:
     
    Fun Department Store - Payroll Evaluation
    Control Text Box Name Caption Other Properties
    Text Box Text Box txtStartDate Start Date:  
    Text Box Text Box txtEndDate End Date:  
    Text Box Text Box EmployeeNumber Employee #:  
    Text Box Text Box txtEmployeeName    
    Button Button cmdEvaluate Evaluate  
    Label Text Box txtHourlySalary Hourly Salary:  
    Label Text Box txtTimeSheetID Time Sheet ID: Visible: No
    Line Line     Border Width: 3 Pt
    Border Color: Text 1, Lighter 50%
    Label Text Box   Monday  
    Label Text Box   Tuesday  
    Label Text Box   Wednesday  
    Label Text Box   Thursday  
    Label Text Box   Friday  
    Label Text Box   Saturday  
    Label Text Box   Sunday  
    Label Text Box   Total  
    Line Line      
    Text Box Text Box txtWeek1Monday Week 1:  
    Text Box Text Box txtWeek1Tuesday    
    Text Box Text Box txtWeek1Wednesday    
    Text Box Text Box txtWeek1Thursday    
    Text Box Text Box txtWeek1Friday    
    Text Box Text Box txtWeek1Saturday    
    Text Box Text Box txtWeek1Sunday    
    Text Box Text Box txtTotalTimeWeek1    
    Line Line      
    Text Box Text Box txtWeek2Monday Week 2:  
    Text Box Text Box txtWeek2Tuesday    
    Text Box Text Box txtWeek2Wednesday    
    Text Box Text Box txtWeek2Thursday    
    Text Box Text Box txtWeek2Friday    
    Text Box Text Box txtWeek2Saturday    
    Text Box Text Box txtWeek2Sunday    
    Text Box Text Box txtTotalTimeWeek2    
    Line Line     Border Width: 3 Pt
    Border Color: Text 1, Lighter 50%
    Label Text Box   Time  
    Label Text Box   Pay  
    Text Box Text Box txtRegularTime Regular:  
    Text Box Text Box txtRegularPay    
    Text Box Text Box txtOvertime Overtime:  
    Text Box Text Box txtOvertimePay    
    Line Line      
    Text Box Text Box txtGrossSalary Gross Salary:  
    Button Button cmdClose Close  

    Fun Department Store - Payroll Evaluation

  5. Right-click the Evaluate button and click Build Event...
  6. In the Choose Builder dialog box, double-click Code Builder
  7. Set the code in the module as follows:
    Option Compare Database
    Option Explicit
    
    Private Sub txtStartDate_LostFocus()
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            txtEndDate = ""
            Exit Sub
        End If
        
        txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
    End Sub
    
    Private Sub txtEmployeeNumber_LostFocus()
    On Error GoTo txtEmployeeNumber_Error
    
        Dim dbFunDS As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            txtEmployeeName = ""
            txtHourlySalary = ""
            Exit Sub
        End If
        
        Set dbFunDS = CurrentDb
        Set rsEmployees = dbFunDS.OpenRecordset("SELECT FirstName, LastName, HourlySalary " & _
                                                "FROM Employees " & _
                                                "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';")
        
            txtEmployeeName = rsEmployees("LastName") & ", " & rsEmployees("FirstName")
            txtHourlySalary = rsEmployees("HourlySalary")
            
            Set dbFunDS = Nothing
            Set rsEmployees = Nothing
            Exit Sub
        
    txtEmployeeNumber_Error:
        If Err.Number = 3021 Then
            MsgBox "Invalid Employee Number: The employee number you entered was not found in the database.", _
                   vbOKOnly Or vbInformation, "Fun Department Store - Employee Time Sheet"
               Exit Sub
        Else
            MsgBox "A problem occurred when trying to retrieve the employee record." & vbCrLf & _
                   "Error #:     " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, _
                   vbOKOnly Or vbInformation, _
                   "Fun Department Store - Employee Payroll"
        End If
        
        Resume Next
    End Sub
    
    Private Sub cmdEvaluate_Click()
        Dim dbFunDS As Database
        Dim rsTimeSheets As Recordset
        
        Dim HourlySalary As Double
        Dim TotalTimeWeek1 As Double
        Dim TotalTimeWeek2 As Double
        
        Dim OvertimeSalary As Double
        Dim RegularTimeWeek1 As Double
        Dim RegularPayWeek1 As Double
        Dim OvertimeWeek1 As Double
        Dim OvertimePayWeek1 As Double
    
        Dim RegularTimeWeek2 As Double
        Dim RegularPayWeek2 As Double
        Dim OvertimeWeek2 As Double
        Dim OvertimePayWeek2 As Double
            
        Dim TotalRegularTime As Double
        Dim TotalOvertime As Double
        Dim TotalRegularPay As Double
        Dim OvertimePay As Double
        Dim TotalEarnings As Double
    
        Dim TimeSheetFound As Boolean
        
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        HourlySalary = 0#
        TotalTimeWeek1 = 0#
        TotalTimeWeek2 = 0#
        RegularTimeWeek1 = 0#
        RegularPayWeek1 = 0#
        OvertimeWeek1 = 0#
        OvertimePayWeek1 = 0#
    
        RegularTimeWeek2 = 0#
        RegularPayWeek2 = 0#
        OvertimeWeek2 = 0#
        OvertimePayWeek2 = 0#
    
        TotalRegularTime = 0#
        TotalOvertime = 0#
        TotalRegularPay = 0#
        OvertimePay = 0#
        TotalEarnings = 0#
    
        TimeSheetFound = False
    
        Set dbFunDS = CurrentDb
            
        ' Get the values in the TimeSheet table
        Set rsTimeSheets = dbFunDS.OpenRecordset("SELECT TimeSheetID, EmployeeNumber, StartDate, " & _
                                                 "Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, " & _
                                                 "Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday " & _
                                                 "FROM TimeSheets1 " & _
                                                 "WHERE EmployeeNumber = '" & txtEmployeeNumber & "' AND StartDate = #" & txtStartDate & "#;")
            
        ' Find out if there was a record in the TimeSheet table that corresponds to the specified employee number and the starting date
        If rsTimeSheets.RecordCount > 0 Then
            ' If there exists a time record for the employee
            ' number and the start date, display its values
            txtTimeSheetID.Visible = True
            txtTimeSheetID = rsTimeSheets("TimeSheetID")
            txtEmployeeNumber = rsTimeSheets("EmployeeNumber")
            txtStartDate = rsTimeSheets("StartDate")
            txtWeek1Monday = rsTimeSheets("Week1Monday")
            txtWeek1Tuesday = rsTimeSheets("Week1Tuesday")
            txtWeek1Wednesday = rsTimeSheets("Week1Wednesday")
            txtWeek1Thursday = rsTimeSheets("Week1Thursday")
            txtWeek1Friday = rsTimeSheets("Week1Friday")
            txtWeek1Saturday = rsTimeSheets("Week1Saturday")
            txtWeek1Sunday = rsTimeSheets("Week1Sunday")
            txtWeek2Monday = rsTimeSheets("Week2Monday")
            txtWeek2Tuesday = rsTimeSheets("Week2Tuesday")
            txtWeek2Wednesday = rsTimeSheets("Week2Wednesday")
            txtWeek2Thursday = rsTimeSheets("Week2Thursday")
            txtWeek2Friday = rsTimeSheets("Week2Friday")
            txtWeek2Saturday = rsTimeSheets("Week2Saturday")
            txtWeek2Sunday = rsTimeSheets("Week2Sunday")
            
            TimeSheetFound = True
            
            TotalTimeWeek1 = CDbl(txtWeek1Monday) + CDbl(txtWeek1Tuesday) + CDbl(txtWeek1Wednesday) + CDbl(txtWeek1Thursday) + CDbl(txtWeek1Friday) + CDbl(txtWeek1Saturday) + CDbl(txtWeek1Sunday)
            TotalTimeWeek2 = CDbl(txtWeek2Monday) + CDbl(txtWeek2Tuesday) + CDbl(txtWeek2Wednesday) + CDbl(txtWeek2Thursday) + CDbl(txtWeek2Friday) + CDbl(txtWeek2Saturday) + CDbl(txtWeek2Sunday)
    
            HourlySalary = CDbl(txtHourlySalary)
            Rem The overtime is paid time and half
            OvertimeSalary = HourlySalary * 1.5
            
            Rem If the employee worked under 40 hours, there is no overtime
            If TotalTimeWeek1 < 40# Then
                RegularTimeWeek1 = TotalTimeWeek1
                RegularPayWeek1 = HourlySalary * RegularTimeWeek1
                OvertimeWeek1 = 0#
                OvertimePayWeek1 = 0#
                Rem If the employee worked over 40 hours, calculate the overtime
            ElseIf TotalTimeWeek1 >= 40# Then
                RegularTimeWeek1 = 40#
                RegularPayWeek1 = HourlySalary * 40#
                OvertimeWeek1 = TotalTimeWeek1 - 40#
                OvertimePayWeek1 = OvertimeWeek1 * OvertimeSalary
            End If
                            
            If TotalTimeWeek2 < 40# Then
                RegularTimeWeek2 = TotalTimeWeek2
                RegularPayWeek2 = HourlySalary * RegularTimeWeek2
                OvertimeWeek2 = 0#
                OvertimePayWeek2 = 0#
            ElseIf TotalTimeWeek2 >= 40# Then
                RegularTimeWeek2 = 40#
                RegularPayWeek2 = HourlySalary * 40#
                OvertimeWeek2 = TotalTimeWeek2 - 40#
                OvertimePayWeek2 = OvertimeWeek2 * OvertimeSalary
            End If
                           
            txtTotalTimeWeek1 = FormatNumber(TotalTimeWeek1)
            txtTotalTimeWeek2 = FormatNumber(TotalTimeWeek2)
                            
            TotalRegularTime = RegularTimeWeek1 + RegularTimeWeek2
            TotalOvertime = OvertimeWeek1 + OvertimeWeek2
            TotalRegularPay = RegularPayWeek1 + RegularPayWeek2
            OvertimePay = OvertimePayWeek1 + OvertimePayWeek2
            TotalEarnings = TotalRegularPay + OvertimePay
                            
            txtRegularTime = FormatNumber(TotalRegularTime)
            txtOvertime = FormatNumber(TotalOvertime)
            txtRegularPay = FormatNumber(TotalRegularPay)
            txtOvertimePay = FormatNumber(OvertimePay)
                           
            txtGrossSalary = FormatNumber(TotalEarnings)
        End If
        
        If TimeSheetFound = False Then
            MsgBox "There is no time sheet for that start date and that employee number.", _
                   vbOKOnly Or vbInformation, _
                   "FunDS - Employees Payroll"
                                        
            ' If no record was found for the employee number in the
            ' specified time frame, get ready to create a new record
            txtTimeSheetID.Visible = False
                
            txtWeek1Monday = "0.00"
            txtWeek1Tuesday = "0.00"
            txtWeek1Wednesday = "0.00"
            txtWeek1Thursday = "0.00"
            txtWeek1Friday = "0.00"
            txtWeek1Saturday = "0.00"
            txtWeek1Sunday = "0.00"
            txtWeek2Monday = "0.00"
            txtWeek2Tuesday = "0.00"
            txtWeek2Wednesday = "0.00"
            txtWeek2Thursday = "0.00"
            txtWeek2Friday = "0.00"
            txtWeek2Saturday = "0.00"
            txtWeek2Sunday = "0.00"
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    
    End Sub
  8. Return to Microsoft Access
  9. Close the form
  10. When asked whether you want to save, click Yes
 
 
 

Overtime and the Daily Time Sheet

Another to calculate overtime considers each day on its own. In this case, a regular work day has 1 to 8 hours. Any period over 8 hours is considered overtime. As a result, an employee can work one or two days in a week and get overtime, just like another employee can work 5 or more days a week but not get overtime.

Practical LearningPractical Learning: Evaluating a Payroll

  1. On the Ribbon, click Create
  2. In the Forms section, click Form Design
  3. Save the form as PayrollEvaluation2
  4. Design a form as follows:
     
    Fun Department Store - Payroll Evaluation
    Control Text Box Name Caption  
    Text Box Text Box txtStartDate Time Sheet Start Date:  
    Text Box Text Box txtEndDate End Date:  
    Text Box Text Box txtEmployeeNumber Employee #:  
    Text Box Text Box txtEmployeeName    
    Button Button cmdFindEvaluate Find Time Sheet and Evaluate  
    Text Box Text Box txtHourlySalary Hourly Salary:  
    Text Box Text Box txtTimeSheetID Time Sheet ID:  
    Line Line     Border Width: 3 Pt
    Label Text Box   Monday  
    Label Text Box   Tuesday  
    Label Text Box   Wednesday  
    Label Text Box   Thursday  
    Label Text Box   Friday  
    Label Text Box   Saturday  
    Label Text Box   Sunday  
    Label Text Box   Total  
    Line Line      
    Text Box Text Box txtWeek1Monday Week 1:  
    Text Box Text Box txtWeek1Tuesday    
    Text Box Text Box txtWeek1Wednesday    
    Text Box Text Box txtWeek1Thursday    
    Text Box Text Box txtWeek1Friday    
    Text Box Text Box txtWeek1Saturday    
    Text Box Text Box txtWeek1Sunday    
    Text Box Text Box txtWeek1TotalTimeWorked    
    Line Line     Border Style: Dashes
    Text Box Text Box txtWk1MonRegularTime Regular Time:  
    Text Box Text Box txtWk1TueRegularTime    
    Text Box Text Box txtWk1WedRegularTime    
    Text Box Text Box txtWk1ThuRegularTime    
    Text Box Text Box txtWk1FriRegularTime    
    Text Box Text Box txtWk1SatRegularTime    
    Text Box Text Box txtWk1SunRegularTime    
    Text Box Text Box txtWeek1TotalRegularTime    
    Text Box Text Box txtWk1MonOvertime Overtime:  
    Text Box Text Box txtWk1TueOvertime    
    Text Box Text Box txtWk1WedOvertime    
    Text Box Text Box txtWk1ThuOvertime    
    Text Box Text Box txtWk1FriOvertime    
    Text Box Text Box txtWk1SatOvertime    
    Text Box Text Box txtWk1SunOvertime    
    Text Box Text Box txtWeek1TotalOvertime    
    Text Box Text Box txtWk1MonRegularPay Regular Pay:  
    Text Box Text Box txtWk1TueRegularPay    
    Text Box Text Box txtWk1WedRegularPay    
    Text Box Text Box txtWk1ThuRegularPay    
    Text Box Text Box txtWk1FriRegularPay    
    Text Box Text Box txtWk1SatRegularPay    
    Text Box Text Box txtWk1SunRegularPay    
    Text Box Text Box txtWeek1TotalRegularPay    
    Text Box Text Box txtWk1MonOvertimePay Overtime Pay:  
    Text Box Text Box txtWk1TueOvertimePay    
    Text Box Text Box txtWk1WedOvertimePay    
    Text Box Text Box txtWk1ThuOvertimePay    
    Text Box Text Box txtWk1FriOvertimePay    
    Text Box Text Box txtWk1SatOvertimePay    
    Text Box Text Box txtWk1SunOvertimePay    
    Text Box Text Box txtWeek1TotalOvertimePay    
    Line Line     Border Width: 3 Pt
    Text Box Text Box txtWeek2Monday Week 2:  
    Text Box Text Box txtWeek2Tuesday    
    Text Box Text Box txtWeek2Wednesday    
    Text Box Text Box txtWeek2Thursday    
    Text Box Text Box txtWeek2Friday    
    Text Box Text Box txtWeek2Saturday    
    Text Box Text Box txtWeek2Sunday    
    Text Box Text Box txtWeek2TotalTimeWorked    
    Line Line     Border Style: Dashes
    Text Box Text Box txtWk2MonRegularTime Regular Time:  
    Text Box Text Box txtWk2TueRegularTime    
    Text Box Text Box txtWk2WedRegularTime    
    Text Box Text Box txtWk2ThuRegularTime    
    Text Box Text Box txtWk2FriRegularTime    
    Text Box Text Box txtWk2SatRegularTime    
    Text Box Text Box txtWk2SunRegularTime    
    Text Box Text Box txtWeek2TotalRegularTime    
    Text Box Text Box txtWk2MonOvertime Overtime:  
    Text Box Text Box txtWk2TueOvertime    
    Text Box Text Box txtWk2WedOvertime    
    Text Box Text Box txtWk2ThuOvertime    
    Text Box Text Box txtWk2FriOvertime    
    Text Box Text Box txtWk2SatOvertime    
    Text Box Text Box txtWk2SunOvertime    
    Text Box Text Box txtWeek2TotalOvertime    
    Text Box Text Box txtWk2MonRegularPay Regular Pay:  
    Text Box Text Box txtWk2TueRegularPay    
    Text Box Text Box txtWk2WedRegularPay    
    Text Box Text Box txtWk2ThuRegularPay    
    Text Box Text Box txtWk2FriRegularPay    
    Text Box Text Box txtWk2SatRegularPay    
    Text Box Text Box txtWk2SunRegularPay    
    Text Box Text Box txtWeek2TotalRegularPay    
    Text Box Text Box txtWk2MonOvertimePay Overtime Pay:  
    Text Box Text Box txtWk2TueOvertimePay    
    Text Box Text Box txtWk2WedOvertimePay    
    Text Box Text Box txtWk2ThuOvertimePay    
    Text Box Text Box txtWk2FriOvertimePay    
    Text Box Text Box txtWk2SatOvertimePay    
    Text Box Text Box txtWk2SunOvertimePay    
    Text Box Text Box txtWeek2TotalOvertimePay    
    Line Line     Border Width: 3 Pt
    Label Text Box   Time  
    Label Text Box   Pay  
    Text Box Text Box txtRegularTime Regular:  
    Text Box Text Box txtRegularPay    
    Text Box Text Box txtOvertime Overtime:  
    Text Box Text Box txtOvertimePay    
    Line Line      
    Text Box Text Box txtGrossSalary Gross Salary:  
    Button Button cmdClose Close  

    Fun Department Store - Payroll Evaluation

  5. Right-click the Evaluate button and click Build Event...
  6. In the Choose Builder dialog box, double-click Code Builder
  7. Set the code in the module as follows:
    Option Compare Database
    Option Explicit
    
    Private Sub txtStartDate_LostFocus()
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        Else
            txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
        End If
    End Sub
    
    Private Sub txtEmployeeNumber_LostFocus()
    On Error GoTo txtEmployeeNumber_Error
    
        Dim dbFunDS As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            Exit Sub
        Else
            Set dbFunDS = CurrentDb
            Set rsEmployees = dbFunDS.OpenRecordset("SELECT EmployeeNumber, FirstName, LastName, HourlySalary " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';")
        
            txtEmployeeName = rsEmployees("LastName") & ", " & rsEmployees("FirstName")
            txtHourlySalary = rsEmployees("HourlySalary")
            
            Set dbFunDS = Nothing
            Set rsEmployees = Nothing
            Exit Sub
        End If
        
    txtEmployeeNumber_Error:
        If Err.Number = 3021 Then
            MsgBox "Invalid Employee Number: The employee number you entered was not found in the database.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
               Exit Sub
        Else
            MsgBox "A problem occurred when trying to retrieve the employee record." & vbCrLf & _
                   "Error #:     " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, _
                   vbOKOnly Or vbInformation, "Kolo Bank"
        End If
        
        Resume Next
    End Sub
    
    Private Sub ProcessPayroll()
        Dim GrossSalary As Double, Exemptions As Double
        Dim SocialSecurity As Double, Medicare As Double
        Dim AllowanceRate As Double, WithheldAmount As Double
        Dim WithheldingAllowances As Double, TaxableGrossWages  As Double
        
        Dim dbFunDS As Database
        Dim rsTimeSheets As Recordset
        
        If IsNull(txtStartDate) Or IsNull(txtEmployeeNumber) Then
            Exit Sub
        Else
            Set dbFunDS = CurrentDb
            Set rsTimeSheets = dbFunDS.OpenRecordset("SELECT TimeSheetID, EmployeeNumber, StartDate, EndDate, " & _
                                                     "Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, " & _
                                                     "Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday " & _
                                                     "FROM TimeSheets1 " & _
                                                     "WHERE StartDate = #" & txtStartDate & "# AND EmployeeNumber = '" & txtEmployeeNumber & "';")
        
            txtTimeSheetID = rsTimeSheets("TimeSheetID")
            txtEmployeeNumber = rsTimeSheets("EmployeeNumber")
            txtStartDate = rsTimeSheets("StartDate")
            txtEndDate = rsTimeSheets("EndDate")
            txtWeek1Monday = rsTimeSheets("Week1Monday")
            txtWeek1Tuesday = rsTimeSheets("Week1Tuesday")
            txtWeek1Wednesday = rsTimeSheets("Week1Wednesday")
            txtWeek1Thursday = rsTimeSheets("Week1Thursday")
            txtWeek1Friday = rsTimeSheets("Week1Friday")
            txtWeek1Saturday = rsTimeSheets("Week1Saturday")
            txtWeek1Sunday = rsTimeSheets("Week1Sunday")
            txtWeek2Monday = rsTimeSheets("Week2Monday")
            txtWeek2Tuesday = rsTimeSheets("Week2Tuesday")
            txtWeek2Wednesday = rsTimeSheets("Week2Wednesday")
            txtWeek2Thursday = rsTimeSheets("Week2Thursday")
            txtWeek2Friday = rsTimeSheets("Week2Friday")
            txtWeek2Saturday = rsTimeSheets("Week2Saturday")
            txtWeek2Sunday = rsTimeSheets("Week2Sunday")
        
            txtEmployeeName = DLookup("LastName", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'") & ", " & DLookup("FirstName", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtAddress = DLookup("Address", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtCity = DLookup("City", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtCounty = DLookup("County", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtState = DLookup("State", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtZIPCode = DLookup("ZIPCode", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtMaritalStatus = DLookup("MaritalStatus", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtExemptions = DLookup("Exemptions", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            txtHourlySalary = DLookup("HourlySalary", "Employees1", "EmployeeNumber = '" & txtEmployeeNumber & "'")
            
            CalculateWeek1Monday
            CalculateWeek1Tuesday
            CalculateWeek1Wednesday
            CalculateWeek1Thursday
            CalculateWeek1Friday
            CalculateWeek1Saturday
            CalculateWeek1Sunday
            CalculateWeek2Monday
            CalculateWeek2Tuesday
            CalculateWeek2Wednesday
            CalculateWeek2Thursday
            CalculateWeek2Friday
            CalculateWeek2Saturday
            CalculateWeek2Sunday
            
            txtWeek1TotalTimeWorked = CDbl(txtWeek1Monday) + CDbl(txtWeek1Tuesday) + CDbl(txtWeek1Wednesday) + CDbl(txtWeek1Thursday) + CDbl(txtWeek1Friday) + CDbl(txtWeek1Saturday) + CDbl(txtWeek1Sunday)
            txtWeek1TotalRegularTime = CDbl(txtWk1MonRegularTime) + CDbl(txtWk1TueRegularTime) + CDbl(txtWk1WedRegularTime) + CDbl(txtWk1ThuRegularTime) + CDbl(txtWk1FriRegularTime) + CDbl(txtWk1SatRegularTime) + CDbl(txtWk1SunRegularTime)
            txtWeek1TotalOvertime = CDbl(txtWk1MonOvertime) + CDbl(txtWk1TueOvertime) + CDbl(txtWk1WedOvertime) + CDbl(txtWk1ThuOvertime) + CDbl(txtWk1FriOvertime) + CDbl(txtWk1SatOvertime) + CDbl(txtWk1SunOvertime)
            txtWeek1TotalRegularPay = CDbl(txtWk1MonRegularPay) + CDbl(txtWk1TueRegularPay) + CDbl(txtWk1WedRegularPay) + CDbl(txtWk1ThuRegularPay) + CDbl(txtWk1FriRegularPay) + CDbl(txtWk1SatRegularPay) + CDbl(txtWk1SunRegularPay)
            txtWeek1TotalOvertimePay = CDbl(txtWk1MonOvertimePay) + CDbl(txtWk1TueOvertimePay) + CDbl(txtWk1WedOvertimePay) + CDbl(txtWk1ThuOvertimePay) + CDbl(txtWk1FriOvertimePay) + CDbl(txtWk1SatOvertimePay) + CDbl(txtWk1SunOvertimePay)
            
            txtWeek2TotalTimeWorked = CDbl(txtWeek2Monday) + CDbl(txtWeek2Tuesday) + CDbl(txtWeek2Wednesday) + CDbl(txtWeek2Thursday) + CDbl(txtWeek2Friday) + CDbl(txtWeek2Saturday) + CDbl(txtWeek2Sunday)
            txtWeek2TotalRegularTime = CDbl(txtWk2MonRegularTime) + CDbl(txtWk2TueRegularTime) + CDbl(txtWk2WedRegularTime) + CDbl(txtWk2ThuRegularTime) + CDbl(txtWk2FriRegularTime) + CDbl(txtWk2SatRegularTime) + CDbl(txtWk2SunRegularTime)
            txtWeek2TotalOvertime = CDbl(txtWk2MonOvertime) + CDbl(txtWk2TueOvertime) + CDbl(txtWk2WedOvertime) + CDbl(txtWk2ThuOvertime) + CDbl(txtWk2FriOvertime) + CDbl(txtWk2SatOvertime) + CDbl(txtWk2SunOvertime)
            txtWeek2TotalRegularPay = CDbl(txtWk2MonRegularPay) + CDbl(txtWk2TueRegularPay) + CDbl(txtWk2WedRegularPay) + CDbl(txtWk2ThuRegularPay) + CDbl(txtWk2FriRegularPay) + CDbl(txtWk2SatRegularPay) + CDbl(txtWk2SunRegularPay)
            txtWeek2TotalOvertimePay = CDbl(txtWk2MonOvertimePay) + CDbl(txtWk2TueOvertimePay) + CDbl(txtWk2WedOvertimePay) + CDbl(txtWk2ThuOvertimePay) + CDbl(txtWk2FriOvertimePay) + CDbl(txtWk2SatOvertimePay) + CDbl(txtWk2SunOvertimePay)
            
            txtRegularTime = CDbl(txtWeek1TotalRegularTime) + CDbl(txtWeek2TotalRegularTime)
            txtOvertime = CDbl(txtWeek1TotalOvertime) + CDbl(txtWeek2TotalOvertime)
            txtRegularPay = CDbl(txtWeek1TotalRegularPay) + CDbl(txtWeek2TotalRegularPay)
            txtOvertimePay = CDbl(txtWeek1TotalOvertimePay) + CDbl(txtWeek2TotalOvertimePay)
            txtGrossSalary = CDbl(txtRegularPay) + CDbl(txtOvertimePay)
            
            AllowanceRate = 76.9
            WithheldAmount = 0#
            GrossSalary = CDbl(txtGrossSalary)
            Exemptions = CDbl(txtExemptions)
            
            WithheldingAllowances = AllowanceRate * Exemptions
            TaxableGrossWages = GrossSalary - WithheldingAllowances
            
            Select Case txtMaritalStatus
                Case "Single"
                    If TaxableGrossWages <= 44# Then
                        WithheldAmount = 0#
                    ElseIf (TaxableGrossWages > 44#) And (TaxableGrossWages <= 222#) Then
                        WithheldAmount = (TaxableGrossWages - 44#) * 10# / 100#
                    ElseIf (TaxableGrossWages > 222#) And (TaxableGrossWages <= 764#) Then
                        WithheldAmount = 17.8 + ((TaxableGrossWages - 222#) * 15# / 100#)
                    ElseIf (TaxableGrossWages > 764#) And (TaxableGrossWages <= 1789#) Then
                        WithheldAmount = 99.1 + ((TaxableGrossWages - 764#) * 25# / 100#)
                    ElseIf (TaxableGrossWages > 1789#) And (TaxableGrossWages <= 3685#) Then
                        WithheldAmount = 355.05 + ((TaxableGrossWages - 1789#) * 28# / 100#)
                    ElseIf (TaxableGrossWages > 3685#) And (TaxableGrossWages <= 7958#) Then
                        WithheldAmount = 886.23 + ((TaxableGrossWages - 3685#) * 33# / 100#)
                    ElseIf (TaxableGrossWages > 7958#) And (TaxableGrossWages <= 7990#) Then
                        WithheldAmount = 2296.32 + ((TaxableGrossWages - 7958#) * 35# / 100#)
                    Else
                        WithheldAmount = 2307.52 + ((TaxableGrossWages - 7990#) * 39.6 / 100#)
                    End If
    
                Case "Married"
                    If TaxableGrossWages <= 165# Then
                        WithheldAmount = 0#
                    ElseIf (TaxableGrossWages > 165#) And (TaxableGrossWages <= 520#) Then
                        WithheldAmount = (TaxableGrossWages - 165#) * 10# / 100#
                    ElseIf (TaxableGrossWages > 520#) And (TaxableGrossWages <= 1606#) Then
                        WithheldAmount = 35.5 + ((TaxableGrossWages - 520#) * 15# / 100#)
                    ElseIf (TaxableGrossWages > 1606#) And (TaxableGrossWages <= 3073#) Then
                        WithheldAmount = 198.4 + ((TaxableGrossWages - 1606#) * 25# / 100#)
                    ElseIf (TaxableGrossWages > 3073#) And (TaxableGrossWages <= 4597#) Then
                        WithheldAmount = 565.15 + ((TaxableGrossWages - 3073#) * 28# / 100#)
                    ElseIf (TaxableGrossWages > 4597#) And (TaxableGrossWages <= 8079#) Then
                        WithheldAmount = 991.87 + ((TaxableGrossWages - 4597#) * 33# / 100#)
                    ElseIf (TaxableGrossWages > 8079#) And (TaxableGrossWages <= 9105#) Then
                        WithheldAmount = 2140.93 + ((TaxableGrossWages - 8079#) * 35# / 100#)
                    Else
                        WithheldAmount = 2500.03 + ((TaxableGrossWages - 9105#) * 39.6 / 100#)
                    End If
                    
                Case Else
                    WithheldAmount = 0#
            End Select
        
            If GrossSalary < 118500# Then
                SocialSecurity = GrossSalary * 6.2 / 100#    ' FY 2015
            Else
                SocialSecurity = 118500# * 6.2 / 100#
            End If
            
            Medicare = GrossSalary * 1.45 / 100#
            
            txtAllowancesCurrent = FormatNumber(WithheldingAllowances)
            txtTaxableGrossSalaryCurrent = FormatNumber(TaxableGrossWages)
            txtFederalIncomeTaxCurrent = FormatNumber(WithheldAmount)
            
            txtSocialSecurityCurrent = FormatNumber(SocialSecurity)
            txtMedicareCurrent = FormatNumber(Medicare)
        End If
    End Sub
    
    Private Sub CalculateWeek1Monday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Monday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1MonRegularTime = FormatNumber(RegularTime)
        txtWk1MonOvertime = FormatNumber(Overtime)
        txtWk1MonRegularPay = FormatNumber(RegularPay)
        txtWk1MonOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Tuesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Tuesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1TueRegularTime = FormatNumber(RegularTime)
        txtWk1TueOvertime = FormatNumber(Overtime)
        txtWk1TueRegularPay = FormatNumber(RegularPay)
        txtWk1TueOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Wednesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Wednesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1WedRegularTime = FormatNumber(RegularTime)
        txtWk1WedOvertime = FormatNumber(Overtime)
        txtWk1WedRegularPay = FormatNumber(RegularPay)
        txtWk1WedOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Thursday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Thursday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1ThuRegularTime = FormatNumber(RegularTime)
        txtWk1ThuOvertime = FormatNumber(Overtime)
        txtWk1ThuRegularPay = FormatNumber(RegularPay)
        txtWk1ThuOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Friday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Friday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1FriRegularTime = FormatNumber(RegularTime)
        txtWk1FriOvertime = FormatNumber(Overtime)
        txtWk1FriRegularPay = FormatNumber(RegularPay)
        txtWk1FriOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Saturday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Saturday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1SatRegularTime = FormatNumber(RegularTime)
        txtWk1SatOvertime = FormatNumber(Overtime)
        txtWk1SatRegularPay = FormatNumber(RegularPay)
        txtWk1SatOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek1Sunday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek1Sunday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk1SunRegularTime = FormatNumber(RegularTime)
        txtWk1SunOvertime = FormatNumber(Overtime)
        txtWk1SunRegularPay = FormatNumber(RegularPay)
        txtWk1SunOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Monday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Monday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2MonRegularTime = FormatNumber(RegularTime)
        txtWk2MonOvertime = FormatNumber(Overtime)
        txtWk2MonRegularPay = FormatNumber(RegularPay)
        txtWk2MonOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Tuesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Tuesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2TueRegularTime = FormatNumber(RegularTime)
        txtWk2TueOvertime = FormatNumber(Overtime)
        txtWk2TueRegularPay = FormatNumber(RegularPay)
        txtWk2TueOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Wednesday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Wednesday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2WedRegularTime = FormatNumber(RegularTime)
        txtWk2WedOvertime = FormatNumber(Overtime)
        txtWk2WedRegularPay = FormatNumber(RegularPay)
        txtWk2WedOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Thursday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Thursday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2ThuRegularTime = FormatNumber(RegularTime)
        txtWk2ThuOvertime = FormatNumber(Overtime)
        txtWk2ThuRegularPay = FormatNumber(RegularPay)
        txtWk2ThuOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Friday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Friday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2FriRegularTime = FormatNumber(RegularTime)
        txtWk2FriOvertime = FormatNumber(Overtime)
        txtWk2FriRegularPay = FormatNumber(RegularPay)
        txtWk2FriOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Saturday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Saturday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2SatRegularTime = FormatNumber(RegularTime)
        txtWk2SatOvertime = FormatNumber(Overtime)
        txtWk2SatRegularPay = FormatNumber(RegularPay)
        txtWk2SatOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub CalculateWeek2Sunday()
        Dim dTimeWorked As Double
        Dim RegularTime As Double, Overtime As Double
        Dim RegularPay As Double, OvertimePay As Double
        
        dTimeWorked = CDbl(txtWeek2Sunday)
        
        If dTimeWorked <= 8# Then
            RegularTime = dTimeWorked
            Overtime = 0#
        Else
            RegularTime = 8#
            Overtime = dTimeWorked - 8#
        End If
        
        RegularPay = RegularTime * CDbl(txtHourlySalary)
        OvertimePay = Overtime * CDbl(txtHourlySalary) * 0.5
        
        txtWk2SunRegularTime = FormatNumber(RegularTime)
        txtWk2SunOvertime = FormatNumber(Overtime)
        txtWk2SunRegularPay = FormatNumber(RegularPay)
        txtWk2SunOvertimePay = FormatNumber(OvertimePay)
    End Sub
    
    Private Sub cmdEvaluate_Click()
        Dim dbFunDS As Database
        Dim rsTimeSheets As Recordset
        
        Dim HourlySalary As Double
        Dim TotalTimeWeek1 As Double
        Dim TotalTimeWeek2 As Double
        
        Dim OvertimeSalary As Double
        Dim RegularTimeWeek1 As Double
        Dim RegularPayWeek1 As Double
        Dim OvertimeWeek1 As Double
        Dim OvertimePayWeek1 As Double
    
        Dim RegularTimeWeek2 As Double
        Dim RegularPayWeek2 As Double
        Dim OvertimeWeek2 As Double
        Dim OvertimePayWeek2 As Double
            
        Dim TotalRegularTime As Double
        Dim TotalOvertime As Double
        Dim TotalRegularPay As Double
        Dim OvertimePay As Double
        Dim TotalEarnings As Double
    
        Dim TimeSheetFound As Boolean
        
        If IsNull(txtStartDate) Or IsEmpty(txtStartDate) Then
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Or IsEmpty(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        HourlySalary = 0#
        TotalTimeWeek1 = 0#
        TotalTimeWeek2 = 0#
        RegularTimeWeek1 = 0#
        RegularPayWeek1 = 0#
        OvertimeWeek1 = 0#
        OvertimePayWeek1 = 0#
    
        RegularTimeWeek2 = 0#
        RegularPayWeek2 = 0#
        OvertimeWeek2 = 0#
        OvertimePayWeek2 = 0#
    
        TotalRegularTime = 0#
        TotalOvertime = 0#
        TotalRegularPay = 0#
        OvertimePay = 0#
        TotalEarnings = 0#
    
        TimeSheetFound = False
    
        Set dbFunDS = CurrentDb
            
        ' Get the values in the TimeSheet table
        Set rsTimeSheets = dbFunDS.OpenRecordset("SELECT TimeSheetID, EmployeeNumber, StartDate, " & _
                                                 "Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday, Week1Saturday, Week1Sunday, " & _
                                                 "Week2Monday, Week2Tuesday, Week2Wednesday, Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday " & _
                                                 "FROM TimeSheets1 " & _
                                                 "WHERE EmployeeNumber = '" & txtEmployeeNumber & "' AND StartDate = #" & txtStartDate & "#;")
            
        ' Find out if there was a record in the TimeSheet table that corresponds to the specified employee number and the starting date
        If rsTimeSheets.RecordCount > 0 Then
            ' If there exists a time record for the employee
            ' number and the start date, display its values
            txtTimeSheetID.Visible = True
            txtTimeSheetID = rsTimeSheets("TimeSheetID")
            txtEmployeeNumber = rsTimeSheets("EmployeeNumber")
            txtStartDate = rsTimeSheets("StartDate")
            txtWeek1Monday = rsTimeSheets("Week1Monday")
            txtWeek1Tuesday = rsTimeSheets("Week1Tuesday")
            txtWeek1Wednesday = rsTimeSheets("Week1Wednesday")
            txtWeek1Thursday = rsTimeSheets("Week1Thursday")
            txtWeek1Friday = rsTimeSheets("Week1Friday")
            txtWeek1Saturday = rsTimeSheets("Week1Saturday")
            txtWeek1Sunday = rsTimeSheets("Week1Sunday")
            txtWeek2Monday = rsTimeSheets("Week2Monday")
            txtWeek2Tuesday = rsTimeSheets("Week2Tuesday")
            txtWeek2Wednesday = rsTimeSheets("Week2Wednesday")
            txtWeek2Thursday = rsTimeSheets("Week2Thursday")
            txtWeek2Friday = rsTimeSheets("Week2Friday")
            txtWeek2Saturday = rsTimeSheets("Week2Saturday")
            txtWeek2Sunday = rsTimeSheets("Week2Sunday")
            
            TimeSheetFound = True
            
            TotalTimeWeek1 = CDbl(txtWeek1Monday) + CDbl(txtWeek1Tuesday) + CDbl(txtWeek1Wednesday) + CDbl(txtWeek1Thursday) + CDbl(txtWeek1Friday) + CDbl(txtWeek1Saturday) + CDbl(txtWeek1Sunday)
            TotalTimeWeek2 = CDbl(txtWeek2Monday) + CDbl(txtWeek2Tuesday) + CDbl(txtWeek2Wednesday) + CDbl(txtWeek2Thursday) + CDbl(txtWeek2Friday) + CDbl(txtWeek2Saturday) + CDbl(txtWeek2Sunday)
    
            HourlySalary = CDbl(txtHourlySalary)
            Rem The overtime is paid time and half
            OvertimeSalary = HourlySalary * 1.5
            
            Rem If the employee worked under 40 hours, there is no overtime
            If TotalTimeWeek1 < 40# Then
                RegularTimeWeek1 = TotalTimeWeek1
                RegularPayWeek1 = HourlySalary * RegularTimeWeek1
                OvertimeWeek1 = 0#
                OvertimePayWeek1 = 0#
                Rem If the employee worked over 40 hours, calculate the overtime
            ElseIf TotalTimeWeek1 >= 40# Then
                RegularTimeWeek1 = 40#
                RegularPayWeek1 = HourlySalary * 40#
                OvertimeWeek1 = TotalTimeWeek1 - 40#
                OvertimePayWeek1 = OvertimeWeek1 * OvertimeSalary
            End If
                            
            If TotalTimeWeek2 < 40# Then
                RegularTimeWeek2 = TotalTimeWeek2
                RegularPayWeek2 = HourlySalary * RegularTimeWeek2
                OvertimeWeek2 = 0#
                OvertimePayWeek2 = 0#
            ElseIf TotalTimeWeek2 >= 40# Then
                RegularTimeWeek2 = 40#
                RegularPayWeek2 = HourlySalary * 40#
                OvertimeWeek2 = TotalTimeWeek2 - 40#
                OvertimePayWeek2 = OvertimeWeek2 * OvertimeSalary
            End If
                
            TotalRegularTime = RegularTimeWeek1 + RegularTimeWeek2
            TotalOvertime = OvertimeWeek1 + OvertimeWeek2
            TotalRegularPay = RegularPayWeek1 + RegularPayWeek2
            OvertimePay = OvertimePayWeek1 + OvertimePayWeek2
            TotalEarnings = TotalRegularPay + OvertimePay
      
            CalculateWeek1Monday
            CalculateWeek1Tuesday
            CalculateWeek1Wednesday
            CalculateWeek1Thursday
            CalculateWeek1Friday
            CalculateWeek1Saturday
            CalculateWeek1Sunday
            CalculateWeek2Monday
            CalculateWeek2Tuesday
            CalculateWeek2Wednesday
            CalculateWeek2Thursday
            CalculateWeek2Friday
            CalculateWeek2Saturday
            CalculateWeek2Sunday
            
            txtWeek1TotalTimeWorked = FormatNumber(TotalTimeWeek1)
            txtWeek1TotalRegularTime = CDbl(txtWk1MonRegularTime) + CDbl(txtWk1TueRegularTime) + CDbl(txtWk1WedRegularTime) + CDbl(txtWk1ThuRegularTime) + CDbl(txtWk1FriRegularTime) + CDbl(txtWk1SatRegularTime) + CDbl(txtWk1SunRegularTime)
            txtWeek1TotalOvertime = CDbl(txtWk1MonOvertime) + CDbl(txtWk1TueOvertime) + CDbl(txtWk1WedOvertime) + CDbl(txtWk1ThuOvertime) + CDbl(txtWk1FriOvertime) + CDbl(txtWk1SatOvertime) + CDbl(txtWk1SunOvertime)
            txtWeek1TotalRegularPay = CDbl(txtWk1MonRegularPay) + CDbl(txtWk1TueRegularPay) + CDbl(txtWk1WedRegularPay) + CDbl(txtWk1ThuRegularPay) + CDbl(txtWk1FriRegularPay) + CDbl(txtWk1SatRegularPay) + CDbl(txtWk1SunRegularPay)
            txtWeek1TotalOvertimePay = CDbl(txtWk1MonOvertimePay) + CDbl(txtWk1TueOvertimePay) + CDbl(txtWk1WedOvertimePay) + CDbl(txtWk1ThuOvertimePay) + CDbl(txtWk1FriOvertimePay) + CDbl(txtWk1SatOvertimePay) + CDbl(txtWk1SunOvertimePay)
                 
            txtWeek2TotalTimeWorked = FormatNumber(TotalTimeWeek2)
            txtWeek2TotalRegularTime = CDbl(txtWk2MonRegularTime) + CDbl(txtWk2TueRegularTime) + CDbl(txtWk2WedRegularTime) + CDbl(txtWk2ThuRegularTime) + CDbl(txtWk2FriRegularTime) + CDbl(txtWk2SatRegularTime) + CDbl(txtWk2SunRegularTime)
            txtWeek2TotalOvertime = CDbl(txtWk2MonOvertime) + CDbl(txtWk2TueOvertime) + CDbl(txtWk2WedOvertime) + CDbl(txtWk2ThuOvertime) + CDbl(txtWk2FriOvertime) + CDbl(txtWk2SatOvertime) + CDbl(txtWk2SunOvertime)
            txtWeek2TotalRegularPay = CDbl(txtWk2MonRegularPay) + CDbl(txtWk2TueRegularPay) + CDbl(txtWk2WedRegularPay) + CDbl(txtWk2ThuRegularPay) + CDbl(txtWk2FriRegularPay) + CDbl(txtWk2SatRegularPay) + CDbl(txtWk2SunRegularPay)
            txtWeek2TotalOvertimePay = CDbl(txtWk2MonOvertimePay) + CDbl(txtWk2TueOvertimePay) + CDbl(txtWk2WedOvertimePay) + CDbl(txtWk2ThuOvertimePay) + CDbl(txtWk2FriOvertimePay) + CDbl(txtWk2SatOvertimePay) + CDbl(txtWk2SunOvertimePay)
                
                
            txtRegularTime = CDbl(txtWeek1TotalRegularTime) + CDbl(txtWeek2TotalRegularTime)
            txtOvertime = CDbl(txtWeek1TotalOvertime) + CDbl(txtWeek2TotalOvertime)
            txtRegularPay = CDbl(txtWeek1TotalRegularPay) + CDbl(txtWeek2TotalRegularPay)
            txtOvertimePay = CDbl(txtWeek1TotalOvertimePay) + CDbl(txtWeek2TotalOvertimePay)
            
            txtGrossSalary = CDbl(txtRegularPay) + CDbl(txtOvertimePay)
        End If
        
        If TimeSheetFound = False Then
            MsgBox "There is no time sheet for that start date and that employee number.", _
                   vbOKOnly Or vbInformation, _
                   "FunDS - Employees Time Sheets"
                                        
            ' If no record was found for the employee number in the
            ' specified time frame, get ready to create a new record
            txtTimeSheetID.Visible = False
                
            txtWeek1Monday = "0.00"
            txtWeek1Tuesday = "0.00"
            txtWeek1Wednesday = "0.00"
            txtWeek1Thursday = "0.00"
            txtWeek1Friday = "0.00"
            txtWeek1Saturday = "0.00"
            txtWeek1Sunday = "0.00"
            txtWeek2Monday = "0.00"
            txtWeek2Tuesday = "0.00"
            txtWeek2Wednesday = "0.00"
            txtWeek2Thursday = "0.00"
            txtWeek2Friday = "0.00"
            txtWeek2Saturday = "0.00"
            txtWeek2Sunday = "0.00"
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    
    End Sub
  8. Close the form
  9. When asked whether you want to save, click Yes
 
 
   
  1. If you want to test the application, open the Employees form and create a few employees records as follows:

    Employee # First Name Last Name Hourly Salary
    283749 Laura Bellius 26.85
    837405 Herbert Mann 14.75
    428041 Jeanine Hewsen 34.05
  2. Close the Employees
  3. Open the TimeSheet form
  4. Create a few time sheet records as follows and click Submit every time you have completed a record:

    Employee # Start Date Wk 1 Mon Wk 1 Tue Wk 1 Wed Wk 1 Thu Wk 1 Fri Wk 1 Sat Wk 1 Sun Wk 2 Mon Wk 2 Tue Wk 2 Wed Wk 2 Thu Wk 2 Fri Wk 2 Sat Wk 2 Sun
    837405 06/01/2015 8 8 8 8 8 0 0 8 8 8 8 8 0 0
    283749 06/01/2015 0 0 0 0 8 8 8 0 0 0 0 8 8 8
    428041 06/15/2015 8 9 9.50 8.50 9 0 0 8 10 8.50 10.50 8 0 0
    283749 06/15/2015 6 6 8 7 8 0 0 7 7 6 8 6 0 0
  5. Close the TimeSheet form
  6. Open the PayrollEvaluation1 form
  7. Enter the Time Sheet Start Date as June 1, 2015
  8. Enter the Employee # as 837405
  9. Click Evaluate:

    Fun Department Store - Payroll Evaluation

  10. Close the PayrollEvaluation1 form
  11. Open the PayrollEvaluation2 form
  12. Enter the Time Sheet Start Date as June 15, 2015
  13. Enter the Employee # as 428041
  14. Click Evaluate:

    Fun Department Store - Payroll Evaluation

  15. Close Microsoft Access
 
 

Home Copyright © 2014-2016, FunctionX, Inc. Home