I have a query and some if/else statements that need to fill in fields in my report when it is opened. This code works fine on a form, but when I add it to the reports it wont run. I'm not sure what OnOpen/OnCurrent/Onwhatever to use to make sure the code runs when the report is open.
Code:
Dim db As Database
Dim rs As Recordset
Dim initPTO As Long
Dim initPTOlastYear As Long
Dim strCriteria As String
Dim strSQL As String
strSQL = ""
strSQL = "SELECT tblEmpData.[Employee#], * FROM tblPTO LEFT JOIN tblEmpData ON tblPTO.pto_emp_id=tblEmpData.[Employee#] WHERE tblPTO.pto_date_used>Forms!frmReports!begDate And tblPTO.pto_date_used<Forms!frmReports!endDate And (((tblEmpData.[Employee#])=Forms!frmReports!selEmployee)) Or (((tblEmpData.TeamAssignment)=Forms!frmReports!selTeamAssignment))"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL)
Me.[Employee#] = rs.Fields("pto_emp_id")
' Current Year PTO Initial Balance Calculations
If (rs.Fields("EmployeeStatus") = "Non-Exempt") Then
initPTO = 160
If (Year(Now) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 25) Then
initPTO = initPTO + 120
ElseIf (Year(Now) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 15) Then
initPTO = initPTO + 80
ElseIf (Year(Now) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 5) Then
initPTO = initPTO + 40
End If
ElseIf (rs.Fields("EmployeeStatus") = "Exempt") Then
initPTO = 200
If (Year(Now) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 25) Then
initPTO = initPTO + 80
ElseIf (Year(Now) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 10) Then
initPTO = initPTO + 40
End If
End If
'Me.PTO_Initial.Value = initPTO
' Previous Year PTO Initial Balance Calculations
If (rs.Fields("EmployeeStatus") = "Non-Exempt") Then
initPTOlastYear = 160
If ((Year(Now) - 1) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 25) Then
initPTOlastYear = initPTOlastYear + 120
ElseIf ((Year(Now) - 1) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 15) Then
initPTOlastYear = initPTOlastYear + 80
ElseIf ((Year(Now) - 1) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 5) Then
initPTOlastYear = initPTOlastYear + 40
End If
ElseIf (rs.Fields("EmployeeStatus") = "Exempt") Then
initPTOlastYear = 200
If ((Year(Now) - 1) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 25) Then
initPTOlastYear = initPTOlastYear + 80
ElseIf ((Year(Now) - 1) - Year(Nz(rs.Fields("CorportateHireDate"))) >= 10) Then
initPTOlastYear = initPTOlastYear + 40
End If
End If
Set rs = Nothing
' SUM up total PTO used this year
strSQL = ""
strSQL = strSQL & "SELECT SUM(pto_hours) as totalPTO FROM tblPTO"
strSQL = strSQL & " WHERE pto_emp_id = Forms!frmReports!selEmployee AND YEAR(pto_date_used) = YEAR(Now)"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL)
'Me.usedPTO = rs.Fields("totalPTO")
rs.Close
Set rs = Nothing
' @@@@@ SUM up total CarryOver available this year (prior to March 15) @@@@@
' Find out how much PTO they used last year, versus their PTO avail last year
strSQL = ""
strSQL = strSQL & "SELECT SUM(pto_hours) as totalPTO from tblPTO"
strSQL = strSQL & " WHERE pto_emp_id = Forms!frmReports!selEmployee AND YEAR(pto_date_used) = (YEAR(Now)-1)"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL)
' If we are not yet past 3/15 this year, show how much is left otherwise they have no CarryOver left
If ((DateDiff("d", Now, "03/15")) <= 0) Then
availCarryOver = 0
Else
availCarryOver = initPTOlastYear - Nz(rs.Fields("totalPTO"))
End If
'Reduce availCarryOver to 40 hours if not below it
If (availCarryOver > 40) Then availCarryOver = 40
'Me.initCarryOver = availCarryOver
rs.Close
Set rs = Nothing
' Determine how many total float hours are available
strSQL = ""
strSQL = strSQL & "SELECT SUM(float_hours) as totalFloat from tblFloat"
strSQL = strSQL & " WHERE float_year = (YEAR(Now))"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL)
'Me.initFloat = rs.Fields("totalFloat")
rs.Close
Set rs = Nothing
