Conditional Formatting of a Field based on the value of a different Field (1 Viewer)

[MSAccessRookie]

I have come across an issue where I need to have conditional Formatting of a Field based on the value of a different Field.

My company produces customized animal food for our clients. When a new Formula is developed, there is a need for Verification of the Formula before it can be put into production. Recently there have been instances where an Unverified Formula was sent to Production and it turned out that it should not have been, and I was asked to prevent this from happening in the future.

To this end, the Products Table (tblProducts) and the Products entry Form (frmProducts) have been modified to include three new Fields:

• ModifiedBy - Integer (FK to tblPeople for Staff Member ID)
• DateModified - DateTime
• FormulaWasVerified - CheckBox (Default=False)

Our Access Program has a Form that is used to create Invoices. Since each Invoice can contain multiple products that are being sold, the Form has a Sub-Form that contains the Invoice Details.

An ideal resolution to the issue would have the Invoice Details Form test the validation Field (FormulaWasValidated) and change the color of the Product_ID Field based on whether or not the Formula for the Product has been verified. This would need to be done on a Product by Product basis. There appear to be at least two problems with this approach.

1. When I try to use Conditional Formatting of the Product_ID Field, there is no obvious way to tie the condition to whether a different Field (FormulaWasValidated) has a value of True or False.
2. When I try to use VB to do the test, it works, but when I modify the background color, ALL Product_ID Fields in the Sub-Form are set to the new color, not just the one that has not been verified.

Does anyone have any other ideas?

 

[boblarson]

In the Conditional formatting, change the VALUE IS to EXPRESSION IS and then you put in

[FieldNameBoundToCheckBoxHere] = 0

 

[MSAccessRookie]

In the Conditional formatting, change the VALUE IS to EXPRESSION IS and then you put in

[FieldNameBoundToCheckBoxHere] = 0

I made the change that you suggested, but for some reason, I could not get that particular syntax to work for me. After playing around for a little while, I was able to get Dlookup() to do what I wanted. A test invoice with more than one Product now only highlights the ones that are not verified.

Thanks for the assist.