Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Color-coded box to display on a report

    I publish a report on the status of our organization's publications. A great many of them need to be rewritten, and that effort will span several months. We deliver a monthly update to our chief of staff, who likes to see this kind of information color-coded: red (we ain't gonna make it), amber (we're struggling), green (mission accomplished). The table that feeds the report is linked to a list in our SharePoint site. The various staff supervisors will decide which code to assign to their publication status. I want them to manage the setting with the list in SharePoint—they will not need to touch the database itself. My first question was how best to enable them to set the red-amber-green status. The easy way, it seemed, was to add three columns (checkbox fields) to the SharePoint list: Red, Amber, Green. That's done.The chief of staff probably will not use the SharePoint list—she will see a printed report once a month. I'll produce that report from the Access database. I want that report to display a small square graphic—a box—with the appropriate color fill beside each publication title. If the department head places the checkmark in the "green" box in the SharePoint list, the printed report will display a green box. I envision using code to control the color for the dot: something like, "If the green field is true, then the color for this dot is green." If the supervisor has not checked any of those boxes, the report should display an empty box with a thin black border.I've placed the little color box on the form and named it "StatusBox." I've also placed the red, amber, and green checkboxes on the form, but set their properties to Visible=No. I think I'm on the right track, but I don't know how to write the code for this color box. Can anyone lend a hand, please. Thanks!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Caesar3 View Post
    My first question was how best to enable them to set the red-amber-green status. The easy way, it seemed, was to add three columns (checkbox fields) to the SharePoint list: Red, Amber, Green. That's done.
    My initial response would be to add a single field that could take any one of those three values. It is always easier to handle data from a single field, than to have it spread across 3 fields. With your current system do you need a system to stop users ticking more than one box?


    Quote Originally Posted by Caesar3 View Post
    I want that report to display a small square graphic—a box—with the appropriate color fill beside each publication title. If the department head places the checkmark in the "green" box in the SharePoint list, the printed report will display a green box. I envision using code to control the color for the dot: something like, "If the green field is true, then the color for this dot is green." If the supervisor has not checked any of those boxes, the report should display an empty box with a thin black border.I've placed the little color box on the form and named it "StatusBox." I've also placed the red, amber, and green checkboxes on the form, but set their properties to Visible=No. I think I'm on the right track, but I don't know how to write the code for this color box.
    You can use Conditional Formatting to change the color of a textbox without needing any code.

    Another option would be to put three different colored graphics on the report and set their visible property according to what you want seen.

    The coding for setting the visibility is quite simple.
    me.StatusBox1.visible = (me.green=true) in the OnFormat event of the relevant report section (probably the Detail section)

    What type of graphic is your box? Is is just created using Access tools or a separate image file?
    Regards
    John



  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Good morning, John, and thanks for the advice.
    I'd overlooked the fact that I can add a field with a list of choices. I've replaced the three separate red/amber/green fields with a single field that lets the user choose from the list. The added benefit, as you hinted, is that now the user can choose just one. My first approach could allow the user to select all three!
    I made the graphic with the Access tool for a rectangle. (I was looking for a circle—I'd forgotten that Access has no way to create a curve.)
    I found I can't use conditional formatting on a rectangle, so I've added three text boxes:
    StatusBoxR (fill color: Red), StatusBoxA (fill color: Yellow), and StatusBoxG (fill color: Green).
    I will set their Visible property to No and stack them one atop the other. (For now, however, I've arranged them in a row so I can see them.) Then I'll use code to tie their visible property to the value in the Pub_Status field.
    If the Pub_Status value = Red, then StatusBoxR will be visible.
    Code is not my strong suite, and I've hit a snag. I set the Pub_Status value for a selection of publications, then ran the report. None of the boxes show up, so I suspect there's a problem with my code—
    Code:
    Option Compare Database
    Option Explicit
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    
    If Me.Pub_Status = Red Then        
              Me.StatusBoxR.Visible = True    
    ElseIf Me.Pub_Status = Amber Then        
               Me.StatusBoxA.Visible = True    
    ElseIf Me.Pub_Status = Green Then       
              Me.StatusBoxG.Visible = True
    End If
    End Sub
    Can you spot the problem?
    Last edited by johnhutchison; 2012-02-01 at 15:51. Reason: Put in paragraph breaks and code tags

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    John (or anyone): I've written my post with several paragraph breaks, yet my post appears all run together! I'm missing something—can you tell me what I'm doing wrong?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Caesar,

    When copying code into a post place tags arround it, e.g. [code] your code here [/code]
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this


    Code:
    Option Compare Database 
    Option Explicit 
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Pub_Status = "Red" Then
               Me.StatusBoxR.Visible = True
               Me.StatusBoxA.Visible = False
               Me.StatusBoxG.Visible = False
    ElseIf Me.Pub_Status = "Amber" Then
                Me.StatusBoxR.Visible = False
                Me.StatusBoxA.Visible = True
                Me.StatusBoxG.Visible = False
    ElseIf Me.Pub_Status = "Green" Then
               Me.StatusBoxR.Visible = False
               Me.StatusBoxA.Visible = False
               Me.StatusBoxG.Visible = True 
    End If 
    End Sub
    * Note the quotes around text values.
    * You need to set the visible back to false when it needs to beh hidden.
    Regards
    John



  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Would this be more or less efficient?
    Code:
    Option Compare Database  
    Option Explicit  
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    With Me
      .StatusBoxR.Visible = iif(.Pub_Status = "Red",True,False)
      .StatusBoxA.Visible = iif(.Pub_Status = "Amber",True,False)
      .StatusBoxG.Visible = iif(.Pub_Status = "Green",True,False)
    End With
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    RG
    It is certainly a more compact way to write it, and more efficient (I expect).
    I usually use an even more compact form:


    Code:
    With Me   
    .StatusBoxR.Visible = (.Pub_Status = "Red")   
    .StatusBoxA.Visible = (.Pub_Status = "Amber") 
      .StatusBoxG.Visible = (.Pub_Status = "Green") 
    End With
    for each line the bit in brackets evaluates to either true or false.

    But I thought the longer form may be easier to follow for someone who says "Code is not my strong suite".
    Regards
    John



  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Thanks. I was trying to figure out how to do it w/o the IIF() now I have a new trick in my code bag. Thanks Again.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    This is great! Thanks, John and RG! I can't wait to get to work in the morning and try this out.

    John, a special thanks to you for editing my post. I had a lot of difficulty using the Lounge with my computer at work. I couldn't see any editing buttons on my screen. Just now, I'm writing from home, and its completely different. All the editing buttons show. I wrote about the problem in more detail over in "The Lounge" forum. I'd really like to know what's behind the behavior I saw.

    I'll let you know my report project turns out. Thanks again, guys!

  11. #11
    New Lounger
    Join Date
    Jan 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Question

    I have used this Conditional Formatting and it works well for what I wanted.

    Would like to look at the code which carrys out what I am doing, does the code appear any where so a person can look at it?

    ceb39usa

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    No, that code is embededin the Access engine and can't be seen. Because it is embeded you get much better performance than if it were implemented in VBA.
    Wendell

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Good afternoon, friends—

    I've placed the VBA code in my report, but none of my little colored boxes show up. I've set the properties sheet for all three boxes to Visible = No, and indeed they do not show. I went into the table and set several records to Pub_Status = Red, but nothing's happening. Just need to figure out the disconnection point. Here's my code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    With Me
        .StatusBoxR.Visible = (.Pub_Status = "Red")
        .StatusBoxA.Visible = (.Pub_Status = "Amber")
        .StatusBoxG.Visible = (.Pub_Status = "Green")
            
    End With
    
    End Sub
    Attached Images Attached Images

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It is hard to know what the problem is from here.

    What is the actual value of Pub_status?

    You could add another line of code, either before or after the With block.

    MsgBox(me.pub_status)

    I am wondering if the value of Pub_status is actually a number, rather than a piece of text.
    Regards
    John



  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Caesar & John,

    Just a shot-in-the-dark here but is Pub_Status a control on the Report? If it is not on the report but only a field in the Table/Query can it be accessed with the "Me." protocol?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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