2015年3月17日 星期二

Access Continuous Form Conditional Formatting enable issue

This tutorial explains how to apply Conditional Formatting in Access Continuous Form and issue with enable.


Access Continuous Form Conditional Formatting


Continuous Form is a Form that shows multiple records in a Single Form. In contract, Single Form shows one record in each page. In VBA, you may change the color of Text Box using If condition, but it only works in Single Form. For Continuous Form, the criteria only checks the first record and apply the format to the rest of all records. To solve this issue, you must use the built-in Conditionally Formatting function. The technique used in applying Conditional formatting on Access Continuous Form is same for Access Report.


Conditional Formatting function can only be used in two Controls: Text Box and Combo Box, which means you cannot apply Conditional Formatting on Label, Button, etc.


Most of the time, Conditional Formatting used in the Text Box of Detail section to check the following items:


– Field Value : Apply formatting if Text Box value equal / not equal to another value


– Expression: Compare Text Box value with another value / expression, apply formatting if TRUE


– Set Focus: Apply formatting when you click on the Text Box (will not work if Enable Property= No)


From my personal experience, Conditional Formatting is not working nicely if you use VBA to set to Row Source of Combobox in On Enter Event , it will cause a flash in Text Box in Detail with Conditional Formatting every time you select Combobox.


How to use Conditional Formatting in Access Continuous Form


Suppose that you want a Text Box in Detail to apply Conditional Formatting, right click on the Text Box, then select Conditional Formatting.


Access_Conditional_Formatting_04


Note that in multiple selection of Control, the option of “Conditional Formatting” will disappear. In that event, navigate to menu bar > Format > Conditional Formatting


Access_Conditional_Formatting_07


 


Click on the New Rule button


Access_Conditional_Formatting_01


 


In the first Combobox, there are three options, select and enter value as appropriate.


Field Value Is – Apply formatting if Text Box value equal / not equal / smaller than / larger than another value


Expression Is – Compare Text Box value with another value / expression, apply formatting if TRUE


Field Has Focus – Apply formatting when you click on the Text Box (will not work if Enable Property= No)


Access_Conditional_Formatting_01


Example of Access Continuous Form Conditional Formatting


In this example, I have a Continuous Form called Form2, which shows a list of registration records, and my goal is to display the whole records in gray color if today > Registration Deadline Text Box. Simply speaking, the Conditional Formatting of all Text Boxes depend only on one Text Box value.


Access_Conditional_Formatting_03


 


First, go to Design View of Form2, then select all Text Box in Detail, navigate to menu bar > Format > Conditional Formatting


Access_Conditional_Formatting_05


Enter Expression


TB_RegDeadline.Value<DateSerial(Year(Now()),Month(Now()),Day(Now()))

Now, change the Font Color to gray color, and then press the enable button.


Note that Conditional Formatting overrides the Enable Property that you set in Text Box Property. Therefore if you have previously set Enable = No, you must press on the enable button in the screen below, then the Preview background color will turn gray (but the actual Text Box background color will not turn gray)


Access_Conditional_Formatting_06


Outbound References


https://support.office.com/en-US/Article/Change-the-appearance-of-a-control-by-using-conditional-formatting-6ba9e9fa-4347-4183-b335-44e43b05e22f?ui=en-US&rs=en-US&ad=US#bmexpressionis


 



Access Continuous Form Conditional Formatting enable issue

沒有留言:

張貼留言