Go to Top

Apply conditional formatting using code

Conditional formatting is a great feature introduced in Access 2000, it allows you to apply different formats to records on a forms based on values and formulas you setup. In this example I’m going to build on a article by Microsoft you can find here.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Colors are better for some…

My client wanted to use colors to quickly see the status of each item, (I would have normally used a drop down box for easy sorting on status, both colors and drop downs have their benefits and drawbacks). I added buttons to each record so that the customer can click on each to change the status as shown below:

Each button assigns a numeric value to the GraphicStatusID field and saves the record so that the user can immediately see the change on the form. Here’s the button code:

Here’s how it looks:

Whole Row Formatting

(Notice there are five buttons above, one color per button. I overcame that obstacle by not using the whole row, read more below.)

Use the Controls Collection to apply formatting

There are many reasons to use code instead of Access’ interface to add formatting conditions, mainly it’s a lot easier to apply the formatting when you have to do many controls on your form. Having it all in one place will make it easy to update the formulas and formating too.

The first step is to use the Tag property to identify which controls on your form will be processed by your code, in this case I used “Conditional”. I then used the code posted here and called the ApplyCondFormatting subroutine on the form’s Load event.

Tag Property

The form has a Controls collection that you can iterate using the For Each method and a control variable, (ctl). Looping through all of the controls allows you to check the Tag property and if there is a match, apply the formating code. The code works if you have only three conditions.

But what if you need to apply more than three formats to a row?

There is no way around the three formatting limit so I decided to take a hybrid approach: Use the first two columns to indicate a color status instead of the entire row. The first column, Vendor, has  three and the PO column has two more for a total of five status colors. When the user clicks on any of the status buttons the color will now move between the first and second column as shown below:

You can see the code for staggered formating here.

This article illustrates the power of the control collection and applying conditional formatting in your code.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

6 Responses to "Apply conditional formatting using code"

  • Farhan
    January 16, 2016 - 9:07 pm Reply

    Hello
    Can any help with Conditional Formatting. i am using three columns. for example if qty in first column is equal to 2nd column then the third column shows clear, but if qty is not equal in first and second column, then third column should shows Pending.
    i am not a developer. Kindly help me, how can i use formatting to get result in third column.
    Thanks

  • Frank R. Ruperto
    December 23, 2013 - 1:13 pm Reply

    Color and display attributes are an important feature of my existing Pawnshop Management application. It helps my users to quickly identify key information, such as: the number of days a pawn has been active is displayed in blinking red color if it’s past due, negative money amounts, customer status and other conditional situations.

  • Darek Zawisko
    April 12, 2013 - 1:36 pm Reply

    “There is no way around the three formatting limit”.
    You can always add more formatting in VBA.

  • Peter Weiler
    November 18, 2011 - 8:52 am Reply

    Great help, however I must be either slow or stupid. I need to just have simple logic in a data sheet view to take an amount and have logic to say if > 100 then make the control red, > 200 yellow , or > 300 green . I did this logic in VB for a standard form this way, on a data sheet lost how to do this.

    Private Sub Form_Current()
    If Process_days < 100 Then
    Process_days.BackColor = vbYellow
    Else

    End If
    End Sub

    I can't use the "easy" conditional formatting that access since this kills performance. We are running access using a SQL data base on the server. When I setup the access conditional formatting you could literally watch the screen get "loaded". One I removed the conditional formatting, not an issue. So I guess VB is the way to do it.

    Peter

  • Juan Soto
    April 4, 2011 - 9:46 pm Reply

    Good to hear! I don’t develop in 2010 since there are some serious issues with backwards compatibility with Access 2007. Still, I would recommend the coding approach instead of manually trying to manage conditions on a form, it’s much easier to update in one spot on your code then on all of the controls.

    Thanks for your comment!

  • grovelli
    April 4, 2011 - 9:42 pm Reply

    “There is no way around the three formatting limit”. Access 2010 allows up to 50 concurrent format conditions so I guess now maybe there is…

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.