Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159

    Colors of Report change based on field/criteria

    Hello, its been awhile since I've had to go to this forum. You all were more than helpful before, so why not try again?

    I was wondering if it were possible to have a report change colors, based on a certain field. I'm quite aware of conditional formatting, but you are very limited on the different formats.

    Here is what I have going on:
    I have 30 different teams, so each team has a different logo, and thus different colors. I would like to have a different background color, header color, text body color, based on what team the report is for. Each team name is a field and is also will appear as the header for the report. (I'm trying to avoid having to create 30 unique reports based on team colors).

    Is this possible?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by yes sir View Post
    so why not try again?
    because you get a goof-off like ME answering your question everytime!

    Quote Originally Posted by yes sir View Post
    Is this possible?
    you bet it is. this is done everyday by web developers. It's the same concept that's used the world over, kind of like those random images you see on website homepage banners? Yeah...

    Store all the info in a table and when the report opens, color all of the sections using VBA. I believe the syntax for sections is:
    Code:
    me.sectionname.backcolor = "#alpha numeric string here, 6 chars long"
    The colors should be endless - however many combinations can be found on a color pallette, which is obviously hundreds of thousands. To give you an example, WHITE is:
    Code:
    #FFFFFF
    and black is:
    Code:
    #000000
    and everything in between.

    If you get that down, you'll be an expert in hexidecimal combinations! We could even go to you if we have a problem in assembly.

  3. #3
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Cool. Had a feeling it could be done.

    So I put everything into a table? So I have 30 teams, and I am looking at having different background colors, font colors, etc. for each.

    So my table would have a team name column, then a column for each section of the report that has a different color, then I write the VBA code to register which team name is being used, then have it set to use the colors from the table based on the team name.

    Sounds like I might be learning a thing or two while doing this.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    here's a sample table:

    Code:
    team	colorhead	colorbody	colorfoot
    1	#FFFFFF	#A7DD89	#FFFFFF
    2	#000000	#888888	#000000
    3	#7EC48A	#FFF000	#444EEE
    and then of course, in VBA when the rpt opens:
    Code:
    me.footer.backcolor = 
    
    dlookup("colorfoot", "table", "[team] = '" & me.teamnamecontrol & "'")
    OR...if vba is stubborn, you might have to concat double quotes in:
    Code:
    me.footer.backcolor = """" & 
    
    dlookup("colorfoot", "table", "[team] = '" & me.teamnamecontrol & "'") & """"
    also too, remember that color refs are mostly alpha numeric, and the letters never go higher than 'F'. It's a hex system. But that shouldn't be relevant. You can get color codes from anywhere.

  5. #5
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Well let's say my table looks like this, where header is the Report Header, Detail, is the report's detail section, and ColorTeamFore refers to font color for the Text Box in my report named 'Team'

    Lets say the name of this table is: NBAteamColors

    Code:
    team       ColorHeader       ColorDetail       ColorTeamFore
    Atlanta Hawks       #CC3333       #01244C       #CCCCFF
    Boston Celtics       #000000       #888888       #000000
    Charlotte Bobcats       #7EC48A       #FFF000       #444EEE
    So if I were to write the code for the Hawks, it I would have something like one of these:
    Code:
    me.reportheader.backcolor = #CC3333
    
    dlookup("ColorHeader", "NBAteamColors", "[Atlanta Hawks] = '" & me.reportheader & "'")
    OR...if vba is stubborn, you might have to concat double quotes in:
    Code:
    me.reportheader.backcolor = ""#CC3333"" & 
    
    dlookup("ColorHeader", "NBAteamColors", "[Atlanta Hawks] = '" & me.reportheader & "'") & """"
    How would I go about writing the code for adding the colors to the other parts of the report (fore color, detail section) and also for adding colors for the other teams?

    Do I use the 'If' and 'End If'? Any tutorials for this stuff?
    Last edited by yes sir; 02-27-2011 at 06:10 PM. Reason: I'm not quite sure how you got your table to look like that, hope you can make sense of mine. 4 columns

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    no bud, there's no tutorials for stuff like this.

    What do you mean "colors for other teams"?? You said before there's one report per team. Or a dynamic one? That's the key, dude. You use the header control, or any others to color it, based on the records in the table. Understand?

    If not, you can upload your file and I might be able to put a sample in there for you.

    by the way, the code in your last post does not correlate to the example I posted. This:
    Code:
    "[Atlanta Hawks] = '" & me.reportheader & "'")
    should be this:
    Code:
    "[team] = '" & me.reportheader & "'")
    given that, 'reportheader' control has the name 'ATLANTAHAWKS' in it. See how that works?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    IMO, right concept, wrong execution. In the source query of the report, you should be able to join the "colors" table to the other data on the team ID. That let's you include the colors in the data, and you can get them from there. That let's you avoid repeated calls to the data with DLookup's. Should be a little more efficient.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by pbaldy View Post
    IMO, right concept, wrong execution. In the source query of the report, you should be able to join the "colors" table to the other data on the team ID. That let's you include the colors in the data, and you can get them from there. That let's you avoid repeated calls to the data with DLookup's. Should be a little more efficient.
    Right on.

  9. #9
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Just in case there is still some confusion I will try to explain what I have and what I want.

    -I have 1 Report that contains two sub-reports.
    -This one report contains the info for all 30 teams and it keeps up with 'Draft Picks' that have been traded amongst the 30 teams.
    -One of the sub-reports keeps up with Draft picks traded away by the team, and the other sub-report keeps up with the Draft picks traded to the team. The two sub-reports
    -I have the report set up in a way, so that when I click a command button on one of my forms it will only display these 'traded draft picks" for the Team in which I select. For example, if I pick the "Atlanta Hawks," then each sub-report will display the data that pertains to the Atlanta Hawks and no other teams data will show.

    -Now, since there are 30 teams, I would like to know if it is possible to create some code that will use different colors for the report header, detail section, text color, etc. based on which team is being displayed. (I will add the code to each sub-report if I see it necessary)

    -Basically, this report is only reader friendly when you view it by team, otherwise, if you try viewing all of the report at once, it mixes all of the data together. Since the Atlanta Hawks are the first team alphabetically then their team name is displayed as the title of the report and their logo is used and it looks like they have hundreds of draft pick transactions going on, when really all the Atlanta Hawks have is three.

    If that doesn't clear anything up let me know, I will be more than glad to attach.

  10. #10
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    this is basically the same, with complexities added in.

    Regardless though, Paul's way is the way to go. Mine is fine, but there's plenty of things wrong with it. This thread is a great example of why I should change my code-minded thinking!

    So make that table and get those relationships going. Thanks to Paul for covering for me here!

  11. #11
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Would it be easier if I kept everything in one table?

    I have a table that has the Teams listed (along with other data), why not just add the columns for the colors to this table and not do any relationships via the query.

  12. #12
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    it doesn't matter how you do it, sir. the result, either way, is one source of data for a report.

  13. #13
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    I wish I knew how to even begin referencing with my code, but I am too much of a beginner to really know how.

    This is what I have been trying, but it isn't working.

    Code:
    Private Sub Report_Load()
        If Me.Team = "Atlanta Hawks" Then
            Me.Detail.BackColor = vbRed
        If Me.Team = "Boston Celtics" Then
            Me.Detail.BackColor = vbGreen
        End If
        End If
    End Sub
    All that does is put the detail sections for every team as red. Obviously, not what I want and not really what you all probably expected me to try.

    Attaching database in a few minutes.

  14. #14
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I forget bud, this is your first database project, right?

    When did you first get this gig from the coach? If I remember right, it's been months since you started. Yes?

  15. #15
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Quote Originally Posted by ajetrumpet View Post
    I forget bud, this is your first database project, right?

    When did you first get this gig from the coach? If I remember right, it's been months since you started. Yes?
    First one.

    Started back in the summer, but he is using a version I completed for him back in early November. I'm working on a newer version that has more features added in.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Image change based on combo box
    By bosve73 in forum Forms
    Replies: 4
    Last Post: 08-09-2010, 02:23 AM
  2. Replies: 4
    Last Post: 01-19-2010, 05:36 AM
  3. Run Report with Prompt for Field Criteria
    By diane802 in forum Reports
    Replies: 4
    Last Post: 01-15-2010, 02:31 AM
  4. query - criteria based on another row field value
    By techexpressinc in forum Queries
    Replies: 1
    Last Post: 05-29-2009, 04:17 PM
  5. Replies: 1
    Last Post: 12-30-2005, 10:23 AM

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