"Holiday" check box not writing to time entry table

[bailey11]

I have created a form for time entry that calculates regular time to 40 hours, OT for over 40 hours. These functions work.

However, I have added a check box "Holiday", when this is checked, the form should fill in "213" as the PayID (0=Regular Time, 1=OT and 213=Double time). This function won't work.

Here is the code I have written so far......

On Error GoTo Err_Sub
Dim rs As DAO.Recordset
Dim DB As DAO.Database
Dim SQL
Dim RegularHours
Dim OvertimeHours
Dim HoursWorked
Dim WTDHours
Dim WTDRegularHours
Dim TodayRegularHours


If IsDate(Me.WorkDate) And IsNumeric(Me.EmployeeID) And Me.ProjectNoCB <> "" And IsNumeric(Me.CostCodeCB) And IsNumeric(Me.Units) And Me.DefaultBillType <> "" Then


Set DB = CurrentDb
Set rs = DB.OpenRecordset("SELECT * FROM tblemployees WHERE EmployeeID = " & Me.EmployeeID, dbOpenForwardOnly)


If DCount("EmployeeID", "tbltimeentry", "EmployeeID = " & Me.EmployeeID & " and WorkDate = #" & Me.WorkDate & "#" & _
" AND ProjectNo='" & Me.ProjectNoCB & "' AND Phase='" & Me.PhaseCB & "' AND ActivityCode='" & Me.CostCodeCB & "'") > 0 Then


If MsgBox("You have already entered time for this date for this employee, job, phase, activity code..... Would you like to overwrite this information?", vbYesNo, "Confirm Overwriting Information") = vbYes Then


' DELETE all records for this employee for the day entered, so we don't have duplicates
SQL = "DELETE FROM tbltimeentry " & _
"WHERE EmployeeID = " & Me.EmployeeID & " and WorkDate = #" & Me.WorkDate & "#" & _
" AND ProjectNo='" & Me.ProjectNoCB & "' AND Phase='" & Me.PhaseCB & "' AND ActivityCode='" & Me.CostCodeCB & "'"

DB.Execute SQL
Else
Exit Sub
End If
Else

End If

WTDHours = DSum("Units", "tbltimeentry", "EmployeeID = " & Me.EmployeeID & _
" AND PayID in ('0','1') AND WorkDate BETWEEN #" & GetWeekStartDate(Me.WorkDate) & _
"# AND #" & GetWeekEndDate(Me.WorkDate) & "#")

WTDRegularHours = DSum("Units", "tbltimeentry", "EmployeeID = " & Me.EmployeeID & _
" AND PayID = '0' AND WorkDate BETWEEN #" & GetWeekStartDate(Me.WorkDate) & _
"# AND #" & GetWeekEndDate(Me.WorkDate) & "#")

TodayRegularHours = DSum("Units", "tbltimeentry", "EmployeeID = " & Me.EmployeeID & _
" AND PayID = '0' AND WorkDate = #" & Me.WorkDate & "#")

If IsNull(WTDHours) Then
WTDHours = 0
End If

If IsNull(WTDRegularHours) Then
WTDRegularHours = 0
End If

If IsNull(TodayRegularHours) Then
TodayRegularHours = 0
End If

HoursWorked = Me.Units
RegularHours = 0


If Me.DefaultBillType = "z" Then
If Me.Holiday <> 0 Then
SQL = "INSERT INTO tbltimeentry (EmployeeID, JobNumber, ActivityCode, WorkDate, Phase, " & _
"PayID, Units, DefaultBillType, Description) " & _
"VALUES (" & Me.EmployeeID & ",'" & Me.ProjectNoCB & "','" & Me.CostCodeCB & _
"',#" & Me.WorkDate & "#,'" & Me.PhaseCB & "','213'," & RegularHours & _
",'" & Me.DefaultBillType & "','" & Me.Description & "')"
DB.Execute SQL

End If

If RegularHours > 0 Then
SQL = "INSERT INTO tbltimeentry (EmployeeID, ProjectNo, ActivityCode, WorkDate, Phase, " & _
"PayID, Units, DefaultBillType, Description) " & _
"VALUES (" & Me.EmployeeID & ",'" & Me.ProjectNoCB & "','" & Me.CostCodeCB & _
"',#" & Me.WorkDate & "#,'" & Me.PhaseCB & "','0'," & RegularHours & _
",'" & Me.DefaultBillType & "','" & Me.Description & "')"

DB.Execute SQL

End If


OvertimeHours = HoursWorked - RegularHours

If OvertimeHours > 0 Then
SQL = "INSERT INTO tbltimeentry (EmployeeID, ProjectNo, ActivityCode, WorkDate, Phase, " & _
"PayID, Units, DefaultBillType, Description) " & _
"VALUES (" & Me.EmployeeID & ",'" & Me.ProjectNoCB & "','" & Me.CostCodeCB & _
"',#" & Me.WorkDate & "#,'" & Me.PhaseCB & "','1'," & OvertimeHours & _
",'" & Me.DefaultBillType & "','" & Me.Description & "')"

DB.Execute SQL
End If
Else
If WTDHours < 40 Then
If WTDHours + HoursWorked <= 40 Then
RegularHours = HoursWorked
Else
RegularHours = 40 - WTDHours
End If


SQL = "INSERT INTO tbltimeentry (EmployeeID, ProjectNo, ActivityCode, WorkDate, Phase, " & _
"PayID, Units, DefaultBillType, Description) " & _
"VALUES (" & Me.EmployeeID & ",'" & Me.ProjectNoCB & "','" & Me.CostCodeCB & _
"',#" & Me.WorkDate & "#,'" & Me.PhaseCB & "','0'," & RegularHours & _
",'" & Me.DefaultBillType & "','" & Me.Description & "')"

DB.Execute SQL

End If

OvertimeHours = HoursWorked - RegularHours

If OvertimeHours > 0 Then
SQL = "INSERT INTO tbltimeentry (EmployeeID, ProjectNo, ActivityCode, WorkDate, Phase, " & _
"PayID, Units, DefaultBillType, Description) " & _
"VALUES (" & Me.EmployeeID & ",'" & Me.ProjectNoCB & "','" & Me.CostCodeCB & _
"',#" & Me.WorkDate & "#,'" & Me.PhaseCB & "','1'," & OvertimeHours & _
",'" & Me.DefaultBillType & "','" & Me.Description & "')"

DB.Execute SQL
End If
End If
rs.close

Else

MsgBox "You have to fill in all boxes before clicking the SAVE button!"
DoCmd.CancelEvent


End If

CheckPriorTimeEntries

Set rs = Nothing
Exit_Sub:
Exit Sub

Err_Sub:
DoCmd.CancelEvent
Resume Exit_Sub

End Sub

 

[bailey11]

Has anyone solved my mystery here?

 

[Remou]

Just guessing, you have:
If Me.Holiday <> 0

How about just
If Me.Holiday

This is assuming that the code gets to the Holiday line.

 

[bailey11]

Holiday is a check box. Even if I check it, it enters the hours as regular time with a PayID of "0" (which represents RT). A code of 213 is what I need.

Do I maybe have it in the wrong place?

 

[Remou]

I ran a quick test that inserted Holiday, Regular or Overtime into a table according to the stage of code reached. What I got was
Holiday Checked:
Holiday
Overtime

Holiday Not Checked:
Overtime

Regular hours is set to 0 in code and I could not see that it would ever be greater than 0, so Regular Hours would never run. I do not know if your SQL is correct, if it is not, the code will exit without an error message.

 

[randy700]

In the past, I've found that the easiest way to find a problem involving this much code is to step through it. Put a stop near the beginning of the code and allow it to execute one step at a time. Use the immediate window often to find the value of SQL. Copy the SQL from the immediate window, put it into a query, and see if it runs properly. If you have a problem with spacing or data type, you will find it here.

Randy

 

[bailey11]

I am a novice and don't know enough to do as you suggest. When I run the code from the SAVE button on the form I have, I get "0" (Regular Time PAYID) no matter what, then I get "1" (OT PayID) on all else. I can't get the 213 to insert as the PayID for Holiday no matter what I try.

THanks for the help anyway.

 

[Remou]

First, comment out this line:
[tt]'On Error GoTo Err_Sub[/tt]
That is, put a single quote at the beginning of the line, as shown.
Next, click on the line:
[tt]If IsDate(Me.WorkDate) And ...[/tt]
And press F9 (Toggle Breakpoint). A big dot should appear in the margin.
Then return to form view, without closing the code window. Now when you press Save, you should jump to the code window at the line that you set the breakpoint for. Pressing F8 will allow you to run one line at a time, to see if there are any problems.
While you are trying debugging, it is worth knowing that Ctrl + G will bring up the immediate window and typing:
[tt]?SQL[/tt]
in that window will give you the current value of SQL, which you can then paste into the SQL view of query designer. This is an easy way to see what is wrong with SQL. Typing ?<nameofthingy> or ?<function> in the immediate window is a quick way of checking a lot of stuff, fields, variables, controls, etc.
Finally, it is very rarely a good idea to have an error event with no message, especially when the code is new.

 

[bailey11]

I finally got it. Thanks everyone for the help.