Formatting an Access Report with VB code.

  • 14 years ago
    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
















  • 14 years ago
    It seems you got an answer
  • 14 years ago

    Actually, that gave me a start, but did not fully work. I have gotten further with the code but the formatting still will not complete. Here is the code specific to my database, that I have...let me know if you may be able to shed some more light on it?

    [CODE]Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

       If Me.CCS_HSSType.Value = "CCS Entry Level" Then
          If Me.CPH.Value > 9 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
       ElseIf Me.CCS_HSSType.Value = "CCS Intermediate Level" Then
          If Me.CPH.Value > 10 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
       ElseIf Me.CCS_HSSType.Value = "CCS Advanced Level" Then
          If Me.CPH.Value > 12 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
       ElseIf Me.CCS_HSSType.Value = "HSS Entry Level" Then
          If Me.CPH.Value > 8 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
       ElseIf Me.CCS_HSSType.Value = "HSS Intermediate Level" Then
          If Me.CPH.Value > 9 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
       ElseIf Me.CCS_HSSType.Value = "HSS Advanced Level" Then
          If Me.CPH.Value > 10 Then
             Me.CPH.Properties(BackColor) = 65535
          Else
             Me.CPH.Properties(BackColor) = 13408767
          End If
        End If
    End Sub
    [/CODE]Thanks





































Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”