Timesheets

Almost any type of business (other than a one-person operation) needs a form for recording employees' work hours and a way to print or electronically distribute the timesheet data. Often a company has used a paper form to record work hours for many years, and the electronic form needs to replicate the paper form. In some cases, there are specific government or industry standard formats that must be used, or the data must be produced in a format that can be imported by a mainframe computer. You can use a preformatted Excel worksheet template to produce timesheets in the exact format you need and fill them with data from Access.

One example of using timesheets in such a fashion is an engineering firm whose employees work on various projects for the company's clients. Because the employees' work hours (except for those assigned to internal projects) will be billed back to the clients, in this case a separate worksheet is needed for each employee's work on a specific project per week, so a single employee might have several timesheets in a week. In the case of (for example) a scientific research establishment, where hours are not billed out to clients, one timesheet per employee, listing multiple projects in a week, would be more appropriate.

The form frmWeeklyTimesheet (shown in Figure 7.9) is an Access front end for entering timesheet data that will be exported to Excel timesheets. This form lets you select an employee, client, and project, and fill in a timesheet for that employee. The assumption is that a separate timesheet is done for each client/project combination, so an employee can have multiple timesheets for a given week.

The cboEmployeeID combo box's row source is a union query that combines data from two queries: qryThisWeeksTimesheets, which lists the timesheets that have been filled in so far this week, and qryNeedTimesheets, which lists the employees who have not yet filled out a timesheet for this week. The resulting list displays all the employees, showing the timesheets that have been filled out so far, as illustrated in Figure 7.10.

FIGURE 7.9

An Access form for entering timesheet data for export to Excel.

S fimWeeklyTimesheet

J Weekly Timesheet

Client/Project:

Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours:

Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours:

Regular Hours: | 0 | Overtime Hours: Total Hours:

Total OT Hours:

Regular Hours: | 0 | Overtime Hours: Total Hours:

Total OT Hours:

FIGURE 7.10

A combo box list showing timesheets for employees.

FIGURE 7.10

A combo box list showing timesheets for employees.

After selecting an employee, the CurrentWeekEnding procedure calculates the week ending date (today, if it is Sunday, otherwise last Sunday) and fills the captions of the seven date labels on the form with the correct day of the week; the Manager name is also displayed in the Manager field (the light blue back color indicates that the text box is locked). (See Figure 7.11.)

NOTE

NOTE

I give locked controls a light blue background (as opposed to a white background for editable controls) to give users a visual cue that they can't enter or edit text in these

FIGURE 7.11

Date information automatically filled in after selecting an employee.

S frmWeeklyTimesheet

Weekly Timesheet

[Callahan, taura[

Client/Project:

Monday, July 3,2006

Tuesday, July 4, 2006

Wednesday, July 5,2006

Thursday, July 6,2006

Friday, July 7,2006

Saturday, JulyS, 2006

Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Total Hours:

Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Total OT Hours:

The CurrentWeekEnding and FillDateControls procedures are listed as follows: Public Function CurrentWeekEnding() As Date On Error GoTo ErrorHandler Dim dteToday As Date dteToday = Date

Do While Weekday(dteToday) <> vbSunday dteToday = dteToday - 1 Debug.Print "Testing " & dteToday Loop

CurrentWeekEnding = dteToday

ErrorHandlerExit: Exit Function

ErrorHandler:

MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

Resume ErrorHandlerExit

End Function

Private Sub FillDateControls()

On Error GoTo ErrorHandler

Dim strFormattedDate As String

Fill week ending and weekday controls with text:

Me![txtWeekEnding].Value = CurrentWeekEnding strFormattedDate = Format(DateAdd("d", -6, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblMondayDate].Caption = strFormattedDate strFormattedDate = Format(DateAdd("d", -5, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblTuesdayDate].Caption = strFormattedDate strFormattedDate = Format(DateAdd("d", -4, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblWednesdayDate].Caption = strFormattedDate strFormattedDate = Format(DateAdd("d", -3, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblThursdayDate].Caption = strFormattedDate strFormattedDate = Format(DateAdd("d", -2, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblFridayDate].Caption = strFormattedDate strFormattedDate = Format(DateAdd("d", -1, _

CDate(Me![WeekEnding])), "dddd, mmmm d, yyyy") Me![lblSaturdayDate].Caption = strFormattedDate strFormattedDate = Format((Me![WeekEnding]), _

"dddd, mmmm d, yyyy") Me![lblSundayDate].Caption = strFormattedDate

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

Additionally, the code runs a make-table query that creates a table for use in the query that is the row source of cboClientProject (see Figure 7.12); initially, the combo box's row source is blank, because otherwise the query could not be run. The row source query is a FindUnmatched query created with the Query Wizard that excludes client/project combinations for worksheets that have already been filled out for the selected employee, so you can't accidentally select the same one twice.

FIGURE 7.12

Selecting a client and project for a timesheet.

Employee: Client/Project: Week ending:

Client/Droiec1

Client Code

Project Code

Arthur'sSteak House - Client Mailing List

28385

115

Halloran and Sons Surveying- Delivery Scheduling

28395

155

Halloran and Sons Surveying-Warehouse Renovation

28395

150

Reliable Insurance Co. - Actuarial Charting

38293

105

Reliable Insurance Co. - Scheduling Application

38293

141

Sam's Auto Repairs - Parts Inventory Database

32829

124

Susie's Notions- Customer Mailing List

34819

117

Susie's Notions - Payroll System

34819

123

If you need to modify the data on an existing timesheet, you can do this later, in the review stage, from fdlgTimesheets.

After the client and project has been selected, the hours can be entered; the totals will recalculate automatically (see Figure 7.13).

FIGURE 7.13

Entering hours on a timesheet.

S frmWeeklyTimesheet

I Weekly Timesheet

Employee: Manager:

Employee: Client/Project: Week ending:

Monday, July 3,2006

Tuesday, July 4, 2006

Wednesday, July 5,2006

Thursday, July 6,2006

Friday, July 7,2006

Saturday, JulyS, 2006

Sunday, July 9,2006

Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Total Hours:

Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Total OT Hours:

In the form footer there are three command buttons: the first ("Clear This Timesheet") clears the timesheet so you can start over; the second ("Save This Timesheet") saves the current timesheet and starts a new record for entering another timesheet; and the third ("Send Timesheets to Excel") opens a dialog form listing the timesheets that have been completed for the current week, for review. The three command button event procedures are listed as follows:

Private Sub cmdClearTimesheet_Click()

On Error Resume Next

Delete record in temp table:

End Sub

Private Sub cmdSendToExcel_Click()

On Error GoTo ErrorHandler

DoCmd.OpenForm FormName:="fdlgTimesheets" DoCmd.Close acForm, Me.Name

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The frmWeeklyTimesheet form is bound to a temp table, tblWeeklyTimesheetTemp, to ensure that data won't be saved to the regular table (tblWeeklyTimesheet) until the user chooses to save it, and required fields have been filled in:

Private Sub cmdSaveTimesheet_Click()

On Error GoTo ErrorHandler

Check that required fields have values, and exit if not:

DoCmd.SetWarnings False

DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Me![cboClientProject].RowSource =

strTitle = "Value required

If Nz(Me![cboEmployeeID].Value) = "" Then strPrompt = "Please select an employee

Then

MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, Title:=strTitle Me![cboEmployeeID].SetFocus GoTo ErrorHandlerExit End If

If Nz(Me![cboClientProject].Value) = "" Then strPrompt = "Please select a client and project" MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, Title:=strTitle Me![cboClientProject].SetFocus GoTo ErrorHandlerExit End If

Save data from temp table to regular table: Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblWeeklyTimesheet") With rst .AddNew

![EmployeeID] = Nz(Me![cboEmployeeID].Value) ![ClientCode] = Nz(Me![cboClientProject].Value) ![ProjectCode] = Nz(Me![txtProjectCode].Value) ![WeekEnding] = Nz(Me![txtWeekEnding].Value) ![ManagerID] = Nz(Me![cboEmployeeID].Column(2)) ![MondayHours] = Nz(Me![txtMondayHours].Value) ![TuesdayHours] = Nz(Me![txtTuesdayHours].Value) ![WednesdayHours] = Nz(Me![txtWednesdayHours].Value) ![ThursdayHours] = Nz(Me![txtThursdayHours].Value) ![FridayHours] = Nz(Me![txtFridayHours].Value) ![SaturdayHours] = Nz(Me![txtSaturdayHours].Value) ![SundayHours] = Nz(Me![txtSundayHours].Value) ![MondayOTHours] = Nz(Me![txtMondayOTHours].Value) ![TuesdayOTHours] = Nz(Me![txtTuesdayOTHours].Value) ![WednesdayOTHours] = _

Nz(Me![txtWednesdayOTHours].Value) ![ThursdayOTHours] = Nz(Me![txtThursdayOTHours].Value) ![FridayOTHours] = Nz(Me![txtFridayOTHours].Value) ![SaturdayOTHours] = Nz(Me![txtSaturdayOTHours].Value) ![SundayOTHours] = Nz(Me![txtSundayOTHours].Value) .Update .Close End With

Delete record in temp table:

DoCmd.SetWarnings False DoCmd.RunCommand acCmdSelectRecord

DoCmd.RunCommand acCmdDeleteRecord

Me![cboEmployeeID].Requery Me![cboClientProject].RowSource = ""

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The dialog form opened from the Send Timesheets to Excel button is shown in Figure 7.14.

FIGURE 7.14

A dialog form for reviewing this week's timesheets.

FIGURE 7.14

A dialog form for reviewing this week's timesheets.

The txtEmployeeID textbox on the datasheet subform on the dialog form has a DblClick event procedure, so you can double-click an employee name to open that timesheet for editing, if necessary:

Private Sub txtEmployeeID_DblClick(Cancel As Integer) On Error GoTo ErrorHandler

Dim IngID As Long

Dim strClientCode As String

Dim strProjectCode As String Dim strSearch As String Dim strSQL As String Dim frm As Access.Form Dim strForm As String

Create a filtered query and run it to create the form's record source:

strForm = "frmSelectedTimesheet" IngID = Nz(Me![EmployeeID]) strClientCode = Nz(Me![ClientCode]) strProjectCode = Nz(Me![ProjectCode]) strSQL = "SELECT tblWeeklyTimesheet.*, " _ & "qryEmployees.EmployeeName, " _ & "qryEmployees.ManagerName, " _ & "qryClientsAndProjects.ClientProject " _ & "INTO tmakSelectedTimesheetTemp " _ & "FROM qryClientsAndProjects " _ & "INNER JOIN (tblWeeklyTimesheet " _ & "INNER JOIN qryEmployees " _ & "ON tblWeeklyTimesheet.EmployeeID = " _ & "qryEmployees.EmployeeID) " _ & "ON (qryClientsAndProjects.ProjectCode = " _ & "tblWeeklyTimesheet.ProjectCode) " _ & "AND (qryClientsAndProjects.ClientCode = " _ & "tblWeeklyTimesheet.ClientCode) " _ & "WHERE tblWeeklyTimesheet.EmployeeID=" _ & lngID & " AND tblWeeklyTimesheet.ClientCode=" & Chr$(3 9) & strClientCode & Chr$(39) _ & " AND tblWeeklyTimesheet.ProjectCode=" _ & Chr$(3 9) & strProjectCode & Chr$(3 9) _ & " AND tblWeeklyTimesheet.WeekEnding = " _ & "CurrentWeekEnding();" Debug.Print "SQL string: " & strSQL DoCmd.SetWarnings False DoCmd.RunSQL strSQL

Open form for editing selected timesheet:

DoCmd.OpenForm FormName:=strForm

Set frm = Forms![frmSelectedTimesheet]

frm.Caption = "Weekly Timesheet for " _

& Me![EmployeeName] DoCmd.Close acForm, Parent.Name

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The frmSelectedTimesheet form is a simplified version of the frmWeeklyTimesheet (see Figure 7.15).

FIGURE 7.15

A form for editing a selected timesheet.

3 Weekly Timesheet for Suyama, Michael

Weekly Timesheet

Client/Project:

Monday, July 10, 2006

Tuesday, July 11, 2006

Wednesday, July 12, 2006

Thursday, July 13,2006

Friday, July 14,2006

Saturday, July 15,2006

Sunday, July 16, 2006

Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Regular Hours: Total Hours:

Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Overtime Hours: Total OT Hours:

Sunday, July 16, 2006

The hours can be edited on this form, and when you are done you can either delete this timesheet record by clicking the "Clear This Timesheet" button, or save the record to the regular tblWeeklyTimesheets table. Clicking the "Send Timesheets to Excel" button reopens the fdlgTimesheets dialog, with updated data.

Clicking the OK button on fdlgTimesheets runs the CreateExcelTimesheets procedure, which creates one Excel worksheet for each timesheet listed in the dialog; one of these timesheets is shown in Figure 7.16.

FIGURE 7.16

An Excel timesheet filled with data from Access.

FIGURE 7.16

An Excel timesheet filled with data from Access.

The CreateExcelTimesheets procedure listed as follows first sets up a DAO recordset of the current week's timesheets, and another recordset for that employees hours. The employee information is entered on the worksheet first, then the code iterates through the Hours recordset, processing the hours for each project and day, both regular hours and overtime, until all have been filled in, and then loops to the next employee record:

Function CreateExcelTimeSheets()

On Error GoTo ErrorHandler

Dim appExcel As Excel.Application

Dim dteWeekEnding As Date

Dim lngCount As Long

Dim lngEmployeelD As Long

Dim n As Long

Dim rngCC As Excel.Range

Dim rngDay As Excel.Range

Dim rngOT As Excel.Range

Dim rngPC As Excel.Range

Dim rngRH As Excel.Range

Dim rngTotal As Excel.Range

Dim rngTotalAbove As Excel.Range

Dim rstAll As DAO.Recordset

Dim rstOne As DAO.Recordset

Dim strDocsPath As String

Dim strEmployeeName As String

Dim strPrompt As String

Dim strQuery As String

Dim strSaveName As String

Dim strSheet As String

Dim strSQL As String

Dim strTemplate As String

Dim strTemplateFile As String

Dim strTemplatePath As String

Dim strTitle As String

Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Dim lbl As Access.Label

Set dbs = CurrentDb Set rstAll = _

dbs.OpenRecordset("qryCurrentTimesheetInfo", _ dbOpenDynaset) rstAll.MoveLast rstAll.MoveFirst lngCount = rstAll.RecordCount If lngCount = 0 Then

MsgBox "No current time sheet records to export" GoTo ErrorHandlerExit Else

Forms![fdlgTimesheets]![lblCreatingWorksheets] Debug.Print lngCount _

& " current time sheet records to transfer to Excel" lbl.Visible = True End If

Get the template path that was selected on the main menu:

strTemplate = _

"Weekly time sheet by client and project.xlt" strTemplatePath = GetWorksheetTemplatesPath() strTemplateFile = strTemplatePath & strTemplate If TestFileExists(strTemplateFile) = False Then strTitle = "Template not found" strPrompt = "Excel template " _

& "'Weekly time sheet by client and project.xlt1" _ & " not found in " & strTemplatePath & ";" _ & vbCrLf _

& "please put template in this folder and try again" MsgBox strPrompt, vbCritical + vbOKOnly, strTitle GoTo ErrorHandlerExit Else

Debug.Print "Excel template used: " _ & strTemplateFile

End If

Get the path for saving workbooks:

strDocsPath = GetWorksheetsPath()

Set a reference to the Excel Application object for use in creating workbooks:

Set appExcel = GetObject(, "Excel.Application")

Do While Not rstAll.EOF

Create a recordset of hours for this employee:

lngEmployeelD = rstAll![EmployeeID] strEmployeeName = rstAll![EmployeeName] dteWeekEnding = CDate(rstAll![WeekEnding]) strQuery = "qfltHours"

strSQL = "SELECT * FROM qryCurrentTimesheetlnfo " _

& "WHERE [EmployeelD] = " & lngEmployeelD & ";" Debug.Print "SQL for " & strQuery & ": " & strSQL lngCount = CreateAndTestQuery(strQuery, strSQL) Debug.Print "No. of items found: " & lngCount If lngCount = 0 Then

MsgBox "No items found; canceling" GoTo ErrorHandlerExit End If

Set rstOne = dbs.OpenRecordset(strQuery, _ dbOpenDynaset)

With rstOne

Count the number of records for this employee:

.MoveLast .MoveFirst lngCount = .RecordCount

Create a new workbook from the template to enter hours:

Set wkb = appExcel.Workbooks.Add(strTemplateFile)

wks.Activate appExcel.Visible = True wks.Range("C3") = ![EmployeeName]

For n = 1 To lngCount

Debug.Print "Record " & n & " for " & strEmployeeName

Process hours for first project.

Check for hours worked on Monday:

Reference:=wks.Range("Monday") Set rngCC = _

appExcel.ActiveCell.Offset(columnoffset:=2) Set rngPC = _

appExcel.ActiveCell.Offset(columnoffset:=3) Set rngRH = _

appExcel.ActiveCell.Offset(columnoffset:=4) Set rngOT = _

appExcel.ActiveCell.Offset(columnoffset:=5) rngCC.Value = ![ClientCode] rngPC.Value = ![ProjectCode] rngRH.Value = ![MondayHours] rngOT.Value = ![MondayOTHours] End If

[Similar code for processing Tuesday through Sunday hours omitted.] Elself n > 1 Then

Process different project hours for the same employee on the same worksheet.

Check for extra hours worked on Monday:

Determine whether any hours were added for this day:

appExcel.GoTo _

Reference:=wks.Range("Monday") Set rngCC = _

appExcel.ActiveCell.Offset(columnoffset:=2)

Go to next day and insert a new row above:

appExcel.GoTo _

Reference:=wks.Range("Tuesday") appExcel.ActiveCell.Select appExcel.Selection.EntireRow.Insert Set rngCC = _

appExcel.ActiveCell.Offset(columnoffset:=2) Set rngPC = _

appExcel.ActiveCell.Offset(columnoffset:=3) Set rngRH = _

appExcel.ActiveCell.Offset(columnoffset:=4) Set rngOT = _

appExcel.ActiveCell.Offset(columnoffset:=5) rngCC.Value = ![ClientCode] rngPC.Value = ![ProjectCode] rngRH.Value = ![MondayHours] rngOT.Value = ![MondayOTHours] Set rngTotalAbove = _

appExcel.ActiveCell.Offset(rowoffset:=-1, _ columnoffset:=6) Set rngTotal = _

appExcel.ActiveCell.Offset(columnoffset:=6) rngTotalAbove.Select

Copy Total formula from cell above:

appExcel.Selection.Copy rngTotal.Select wks.Paste appExcel.CutCopyMode = False Else

Enter hours in regular Monday row:

appExcel.ActiveCell.Offset(columnoffset:=3) Set rngRH = _

appExcel.ActiveCell.Offset(columnoffset:=4) Set rngOT = _

appExcel.ActiveCell.Offset(columnoffset:=5) rngCC.Value = ![ClientCode] rngPC.Value = ![ProjectCode] rngRH.Value = ![MondayHours] rngOT.Value = ![MondayOTHours]

[Similar code for processing Tuesday through Sunday hours omitted.]

Save and close filled-in worksheet.

Create workbook save name from employee name and week ending date:

strSaveName = strDocsPath & strEmployeeName & " time sheet for week ending " _ & Format(dteWeekEnding, "d-mmm-yyyy") Debug.Print "Time sheet save name: " _ & strSaveName

On Error Resume Next If there already is a saved worksheet with this name, delete it:

On Error GoTo ErrorHandler wkb.SaveAs FileName:=strSaveName, FileFormat:=xlWorkbookDefault wkb.Close

End With rstAll.MoveNext Loop rstAll.Close rstOne.Close appExcel.Visible = False

End If

End If

.MoveNext

Next n

Kill strSaveName

Set appExcel = Nothing

MsgBox "All time sheet workbooks created in " _ & strDocsPath

ErrorHandlerExit: Exit Function

ErrorHandler:

Excel is not running; open Excel with CreateObject:

If Err.Number = 429 Then

Set appExcel = CreateObject("Excel.Application") Resume Next Else

& "; Description: " Resume ErrorHandlerExit End If

End Function

This procedure creates a new Excel worksheet from a template for each record. This template is pre-filled with standard text, colors, and other features; all it needs is to have the timesheet data filled in from the Access record.

0 0

Post a comment