Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Formatting (Access 2000)

    I have a problem in formatting certain fields in the detail section of a report. What I want to achieve is this:
    For each row in the detail section of the report I want to highlight certain fields if they are greater than a certain percentage of another field. The code I have come up with so far is

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Section("Detail").Controls
    If Left(ctrl.Properties("Name"), 4) = "SCLH" Then
    If SCANHR <> 0 Then
    If ctrl.Value / SCANHR > dblVar Then
    ctrl.Properties("BackColor") = vbYellow
    Else
    ctrl.Properties("BackColor") = vbWhite
    End If
    End If
    End If
    Next
    End Sub

    However, if one field meets the criteria all subsequent rows pick up the formatting even if they don't meet the criteria.
    What am I missing?

    Graham

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Formatting (Access 2000)

    You might see if you can do this with conditional formatting, formula option. You wouldn't need code then.

    In your code, you only reset the background color to white in the inner If Then Else End If block. If the first or second condition aren't met, you don't reset the color. You can correct this by setting the background color to white at the beginning of the code, and only set it to yellow if all conditions are met:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Section("Detail").Controls
    ' Set white as default background color
    ctrl.BackColor = vbWhite
    If Left(ctrl.Properties("Name"), 4) = "SCLH" Then
    If SCANHR <> 0 Then
    If ctrl.Value / SCANHR > dblVar Then
    ' Set background to yellow if all conditions are met
    ctrl.BackColor = vbYellow
    End If
    End If
    End If
    Next
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •