Formatting Calculated values in Access Reports using code

[nuschool33]

Ok all, bear with me. I have created a simple database that holds agent statistics for a daily scorecard. There are 6 different types of employees and each typ e has a certain criteria to meet.

I.E. - An entry level employee must meet a quota of 8 calls per hour (CPH), If he/she meets it then they "Meet Expectations" if it is below then they "Need Improvement"

I have a report tht lists all employees, their employee type (entry, intermediate, advanced, etc) and their "CPH".

I would like their CPH to be color coded based on meeting criteria or not. It would be easy if they all had to meet the same criteria (I would use conditional formatting)but since there are 6 different types, is there a way to write code for this.

Example in plain words:
IF "employee type=entry level"
Then follow these guidelines to color code values.

I know this may be choppy so let me know what detailed information you would need from me. If you want to really help I wouldn't mind sharing my db by email and having you take a look, it is just a very basic database.

Please let me know if you can help.

Thanks
Nuschool33

 

[rjoubert]

In the Format event of your report's Detail section, add code to check the values of your employee type and CPH fields, and set the forecolor of your CPH field accordingly. Note: You may need to change the code below to reference the right fields on your report, and to reference the right values for the employee type and CPH values.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If Me.EmployeeType.Value = "Entry" Then
      If Me.CPH.Value < 8 Then
         Me.CPH.Properties("ForeColor") = 255
      Else
         Me.CPH.Properties("ForeColor") = 0
      End If
   ElseIf Me.EmployeeType.Value = "Intermediate" Then
      If Me.CPH.Value < ?? Then
         Me.CPH.Properties("ForeColor") = 255
      Else
         Me.CPH.Properties("ForeColor") = 0
      End If
   ElseIF Me.EmployeeType.Value = "Advanced" Then
      If Me.CPH.Value < ?? Then
         Me.CPH.Properties("ForeColor") = 255
      Else
         Me.CPH.Properties("ForeColor") = 0
      End If
   End If
End Sub

 

[nuschool33]

rjoubert,

Thanks, i think this is going to get me headed in the right direction. But I one other question, in the case of the code "Me.EmployeeType.Value" - I know what the Employee Type and Value is there for, what is the "Me." supposed to represent. Is it the table the data comes from or just access/vb code?

Please Advise.

Again Thanks for the help.

Nuschool33