run code on report open

[viveleroi]

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.

    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

 

[Uncle Gizmo]

you can't change the data on the report, you can only change the format, and even that's quite difficult unless you follow some particular rules. However you can change the query the report is based on, so I suggest you adapt your code to change a query and then base your report on that query.