MS Access - Forms - Conditional Formatting Based Upon Header Field Value

Posted on 2007-08-07
Last Modified: 2013-11-28
Conditional formatting:

I'd like to format cells in a tabular form based upon the value in a header field. I've reviewed the conditional formatting dialog and it appears that I can only format a cell based upon its contents.

Is there a way for me to format tabular form fields based upon a value in a header field?

Question by:advlegals
    LVL 10

    Expert Comment

    Hi advlegals,

    I am not sure that this is possible.  

    If you format a field in a Tabular it will format All the entries in the column iun the same way, not each field individually.



    Author Comment

    Hi OnALearningCurve.

    That's fine. I do want all fields within the tabular form to be formatted based upon the value in a header field.

    The scenario is this:

    If my header field reads Sat or Sun then my tabular field background color will be yellow.

    LVL 75

    Accepted Solution

    Ok ... here is the basic info

    First ... a short tutortial

    And then from the Access Help File:

    Apply or change conditional formatting on a control
    Open the form in Design view or Form view, or the report in Design view.
    Note  You cannot apply conditional formatting to a control on a data access page.

    Do one of the following:
    If you are in Design view, click the control for which you want to apply conditional formatting.
    If you are in Form view, make sure the control has focus.
    On the Format menu, click Conditional Formatting.
    Do one or more of the following:
     Change the format of a control when it meets certain criteria

    Do one of the following:
    To use values in the selected control as the formatting criteria, click Field Value Is in the first box, click the comparison phrase in the second box, and then type a value in the third (and fourth) box. You can enter a constant value or an expression.

    To use an expression as the formatting criteria (to evaluate data or a condition other than the values in selected controls), click Expression Is in the first box, and then enter the expression in the second box. The expression must return a logical value of TRUE or FALSE.

    Select the font style, color, or other formatting that you want to apply. Microsoft Access applies the selected formatting only if the control value meets the condition, or the expression returns a value of TRUE.
    To add another condition, click Add, and then repeat steps 1 and 2.
     Change the color of the control that has focus

    In the Condition x area of the Conditional Formatting dialog box, click Field Has Focus in the first box.
    Select the font style, color, or other formatting that you want the field to have when it has focus.
     Change, add, or remove a condition

    To change an existing condition, select the font style, color, or other formatting that you want to apply.
    To add a new condition, click Add.
    To remove one or more conditions, click Delete, and then select the check box for the condition or conditions you want to delete.
    Click OK.

    When you open the Conditional Formatting dialog box, it displays the current default formatting for the selected control, including colors, font styles, and any expressions that you may have defined for the field.

    You can specify up to three conditions for each control.

    Conditional formatting is not preserved when you output or export a form or a report to another file format.

    If none of the specified conditions are true, the control keeps its existing formatting. If more than one specified condition is true, Access applies only the formatting of the first true condition.

    Note that doing CF in VBA code allows you a lot more flexibility because you can tap into the CF object model ... and thus have more than 3 conditions and control many other properties.



    That is .. .in the text box, create two Conditional Formatting  'Expression Is'  cases, one for Red and one for Green.  I just tried that.

    So ... click on your memo box (a text box, right?) in design view ... and select Conditional Formatting (CF) like you did with the combo box.

    For Condition 1, select  Expression Is and enter this expression:
    [YourComboBoxName]="Signed Out"

    were 'YourComboBoxName' is the name of your Combo box.

    Select the Red color for this condition.

    Click the Add button to add another expression

    For Condition 1, select  Expression Is and enter this expression:

    Select the Green color

    Click OK

    So ... when you Select Signed Out from you combo box, text in the memo box will turn red ... and when you select Returned, the text will turn Green.

    LVL 10

    Expert Comment


    I have to learn to read questions!!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now