Results 1 to 7 of 7
  1. #1
    odlarhg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    5

    How to change the background color of a report based on the value of a field in the report?

    Hello everyone,



    I want to request your help to be able to change the background color of a report using code.

    I have used these lines of code in Detail_Format

    Me.Detail.BackColor = RGB (255, 188, 84)
    Me.Detail.AlternateBackColor = RGB (255, 188, 84)

    Those lines do indeed change the color without a problem, but now I need to change the background color based on the content of the Language field. There are 4 languages ​​that I have in the database and the color should change depending on the name of each language. I've tried this code, but it just doesn't change the color and the background goes blank.

    If Me.Language = "Spanish" Then
    Me.Detalle.BackColor = RGB (255, 188, 84)
    Me.Detalle.AlternateBackColor = RGB (255, 188, 84)
    End If

    I have tried using Case statements instead of If but the result is always the same. I don't have much experience with VBA within Access, so I don't know if there is a specific way to accomplish this.

    The report is based on a query.


    Beforehand thank you very much. (Sorry if I make mistakes when trying to explain what I need to do. I speak little English.)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i put my code into ON PRINT event:
    Code:
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If Me.Language = "Spanish" Then
        Me.Detail.BackColor = vbYellow
        Me.Detail.AlternateBackColor = RGB(255, 188, 84)
    Else
        Me.Detail.BackColor = vbRed   'RGB(255, 188, 84)
        Me.Detail.AlternateBackColor = RGB(255, 188, 84)
    End If
    End Sub
    tho you have the same colors assigned in your example

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are using English and Spanish code in same db? That doesn't quite make sense to me. I thought a db could be set for only 1 language. But I don't work with foreign language db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    odlarhg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    5
    Hello

    Sorry for the mistake, I work with db in Spanish and speak little English. I did not make all the code changes to make them read in English.

    The table contains records in which there are 4 different languages.

    Spanish
    English
    French
    German

    Currently the code is like this

    If Me.Language = "Spanish" Then
    Me.Detail.BackColor = RGB (255, 188, 84)
    Me.Detail.AlternateBackColor = RGB (255, 188, 84)
    End If
    If Me.Language = "English" Then
    Me.Detail.BackColor = RGB (255,204,255)
    Me.Detail.AlternateBackColor = RGB (255,204,255)
    End If
    If Me.Language = "French" Then
    Me.Detail.BackColor = RGB (204, 255, 255)
    Me.Detail.AlternateBackColor = RGB (204, 255, 255)
    End If
    If Me.Language = "German" Then
    Me.Detail.BackColor = RGB (255, 166, 130)
    Me.Detail.AlternateBackColor = RGB (255, 166, 130)
    End If

    The report should show one record for each page and the background color should change based on the value of the Language field.

    In advance thanks for the help.

  5. #5
    odlarhg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    5
    Hello,

    Thanks for your help. I just copied a section of the code.

    Currently the code is this

    If Me.Language = "Spanish" Then
    Me.Detail.BackColor = RGB (255, 188, 84)
    Me.Detail.AlternateBackColor = RGB (255, 188, 84)
    End If
    If Me.Language = "English" Then
    Me.Detail.BackColor = RGB (255,204,255)
    Me.Detail.AlternateBackColor = RGB (255,204,255)
    End If
    If Me.Language = "French" Then
    Me.Detail.BackColor = RGB (204, 255, 255)
    Me.Detail.AlternateBackColor = RGB (204, 255, 255)
    End If
    If Me.Language = "German" Then
    Me.Detail.BackColor = RGB (255, 166, 130)
    Me.Detail.AlternateBackColor = RGB (255, 166, 130)
    End If

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Seems code should work.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    odlarhg is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    5
    Hi,

    This code Works. Thank you for your help

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Select Case Trim(Me.
    Language
    )
    Case Is = "Spanish"
    Me.
    Detail
    .BackColor = RGB(255, 188, 84)
    Me.
    Detail
    .AlternateBackColor = RGB(255, 188, 84)
    Case Is = "English"
    Me.
    Detail
    .BackColor = RGB(204, 255, 255)
    Me.
    Detail
    .AlternateBackColor = RGB(204, 255, 255)
    Case Is = "French"
    Me.
    Detail
    .BackColor = RGB(255, 204, 255)
    Me.
    Detail
    .AlternateBackColor = RGB(255, 204, 255)
    Case Is = "German"
    Me.
    Detail
    .BackColor = RGB(255, 166, 130)
    Me.
    Detail
    .AlternateBackColor = RGB(255, 166, 130)
    Case Else
    Me.
    Detail
    .BackColor = RGB(255, 255, 255)
    Me.
    Detail
    .AlternateBackColor = RGB(255, 255, 255)
    End Select
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-25-2018, 04:55 PM
  2. Report Background Color using VBA
    By baba in forum Reports
    Replies: 2
    Last Post: 06-21-2015, 07:26 PM
  3. Replies: 7
    Last Post: 06-26-2014, 01:41 PM
  4. Change Color of Box on Report based off Value
    By Nuke1096 in forum Access
    Replies: 10
    Last Post: 01-29-2014, 02:47 PM
  5. Replies: 4
    Last Post: 12-25-2011, 06:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums