Print Page | Close Window

ConditionalFormatting.mdb (beginner)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=375
Printed Date: 20 Oct 2017 at 6:41am


Topic: ConditionalFormatting.mdb (beginner)
Posted By: Roger Carlson
Subject: ConditionalFormatting.mdb (beginner)
Date Posted: 01 Aug 2008 at 7:03am

Please consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - 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):
http://www.rogersaccesslibrary.com/forum/uploads/5/ConditionalFormatting2k.zip" rel="nofollow - ConditionalFormatting2k.zip

Access 2010 (.accdb): uploads/5/ConditionalFormatting2010.zip" rel="nofollow - ConditionalFormatting2010.zip

This does not work with Access 97 and earlier.  For those, see my sample http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=387" rel="nofollow - Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Replies:
Posted By: Roger Carlson
Date Posted: 10 Jun 2016 at 10:02am
Updated sample with complete documentation.

-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window