Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - ConditionalFormatting.mdb (beginner)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

ConditionalFormatting.mdb (beginner)

 Post Reply Post Reply
Author
Message
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 261
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: ConditionalFormatting.mdb (beginner)
    Posted: 01 Aug 2008 at 7:03am

Please consider donating $1 per sample downloaded, find out why here.

ConditionalFormatting

Traditionally, conditional formatting in Access was accomplished through the use of VBA code.  If I wanted to change the format of a field based on it’s value, I’d do something like this to change the background color of the Balance field based on its value.

Select Case Me!txtBal
   Case Is < 2500
      Me!txtBal.BackColor = vbWhite
   Case Is < 5000
      Me!txtBal.BackColor = vbGreen
   Case Is < 7500
      Me!txtBal.BackColor = vbYellow
   Case Is >= 7500
      Me!txtBal.BackColor = vbRed
End Select

or maybe this to format fields based on the value in another field (Check14).

If Me!Check14 = True Then
  Me!txtDesc.BackColor = vbRed
  Me!Acct.ForeColor = vbRed
Else
  Me!txtDesc.BackColor = vbBlue
  Me!Acct.ForeColor = vbBlue
End If

This works well in Single Form view, but not so well in Continuous Form View or Datasheet View.

The problem is that formatting with VBA affects all records the same. This is fine in Single Form view, because you can only see one.

So to solve this problem, Microsoft introduced Conditional Formatting to Access Forms and Reports. I’m going to concentrate on forms here, but the same applies to reports.

Conditional Formatting is built into the form controls themselves.  So to set it, you need to open the form in Design View to get at the control properties.

You need to select the control (in this case a textbox) , the FORMAT tab on the Ribbon, and then Conditional Formatting.

In this sample, I concentrate on comparing to values within the current record, so I’ll leave the top rule type selected.

The next step is to choose the Field Value Is drop down box.  It will have 3 choices: ....

To read more, download the sample.
 
Download
Access 2003 (.mdb):
ConditionalFormatting2k.zip

Access 2010 (.accdb): ConditionalFormatting2010.zip

This does not work with Access 97 and earlier.  For those, see my sample ChangingColorFields.mdb.

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 261
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Posted: 10 Jun 2016 at 10:02am
Updated sample with complete documentation.
Consider donating $1 per sample downloaded, find out why here.<
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.