Conditional formatting in continuous form, based on other table

Question

I have a continuous form (Frm-Invoeren) for entering/displaying values of a table (Tbl-Analyses) with results of analyses done on our raw materials. Ofcourse these values should be within given specs. These specs are in a another table (Tbl-Specs).
The link between these tables is the field "ProdCode".

I would like to apply conditional formatting (change color of text or background) on the textboxes in Frm-Invoeren (displaying the values of Tbl-Analyses) based on the corresponding values in Tbl-Specs.

How do I setup the rules for conditional formatting?

Answer 1

Good Morning ArnoJeroen,

1. Open your Form in Design View
2. Select the Field you want the conditional formatting on
3. Choose the Format Tab in the Ribbon
4. Using the rules manager, create a new rule based on the criteria you need.

In your case

1. Select a rule type - Check values in the current record
2. Format only Cells drop down selection - Expression Is
3. Enter an Expression in the Field like - [Your second Field] >= some value
4. Using the preview section select the formatting you desire
5. Click OK
6. Run the Form for test

Hope this helps

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 2

Hi Chris,

Thanks for your reply.
But, I think I was not totally clear what I want.

The field in "Tbl-Analyses" that has to be validated/formatted e.g. "Moisture" has to be compared to 2 fields "MinMoisture" and "MaxMoisture" in a different table "Tbl-Specs". There should be some "link" between field "ProdCode" in table "Tbl-Analyses" and field "ProdCode" in table "Tbl-Specs". How to get that linkage in the expression for the conditional formatting is the issue for me.

Could you help me?

Kind regards,
Arno

Answer 3

Hi Arno,

If you have your Table relationships setup, then all you have to do in the expression builder (you open this by clicking the ellipses or ... next to where you might manually create the expression) of the Rule manager. You can select the fields you want directly from the Table or you can add the Fields to the Form (change the property, visible = NO). either method should work fine. then just place your expression in the builder ie

(([YourField] <<expression>> [CompareValueField1]) And ([YourField] <<Expression>> [CompareValueField2])

or

(([ListWords]=True) And ([Words]>="ab"))

You'll have to play around with that to get it right for you since I don't know your db nor your values.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 4

Actually I think this would probably work better for you...

(([YourFirstCompareField]>="Your Min value" And [YourFirstCompareField]<= "Your Max Value") And ([YoursecondCompareField]>="Your Min value" And [YourSecondCompareField]<= "Your Max Value"))

In the above the "" around your value are only needed if the values are text.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 5

A quick example of how you can set this up including four Tables, 1 Query and 1 Form. Also relationships are set up as well as the conditional formatting. Click on the link to download the example from my OneDrive. Let me know if you have any questions.

One Drive Link to file

Screen Shot of results and db content

Hth

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 6

Thanks, this helped we on the way to the solution!

Answer 7

Thanks for this detailed explanation.
I used (before seeing your example) only 2 tables, 1 with the analyses, and 1 with the specs. The relationship is set on the fields "ProdCode" in both tables, and I used the fields from both tables in one form without using a query.
What would be the advantage of using a query?

Answer 8

Hi ArnoJeroen,

This issue is a second issue which is different from the original one, I suggest you post a new thread for this issue, 
and there would be more community members to help you and we could focus on the specific issue.

Thanks for your understanding.

Answer 9

I might be reading more into what you have but it seemed to me that your Tables were not normalized to cover the possibility of many to many relationships which the data portends to have. So I added the Tables to cover that and then as a general rule I always use Queries rather than Tables for the source of Objects if for no other reason just in my mind to verify the relationships are going to allow updatability. Sometimes you can end up with a really good solution that simply doesn't work and rather than build a Form first I test data changes in a Query to verify I'm going to get what I expect. Also a query can bring together different data into a single source eliminating the need for subForms? well maybe.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012