Textbox Conditional Formatting based on a Table

[doucet1963]

I looked at a lot of forums and still cannot find a solution to my conditional formatting issue and I have been at it for more than a month. The VBA codes that I tried do not work!
In Access 2010, I have a continuous form that is generated from a query. In the form there is a locked textbox named [organization], the textbox is part of a table named [Tbl_Location]. The [Tbl_Location] is in a relationship with a table named [Tbl_Document] by a field named [OrganizationID]. That last table tracks the organizations status i.e. “good” or “bad” and when the organizations status change from a “Bad” to a “Good” status then a checkbox is used to identify the valid one. i.e. a checkbox that is selected means that the status is no longer valid while an unchecked checkbox in the table means the new status is valid.
I want to highlight on the form the [organizations] based on a “Good” [Status] when the [textbox] is unchecked. I do not want to highlight the organization that do not meet these conditions.
The VBA could be something like this”
Private Sub Form_Load()
If [tbl_Document].[status]=”Good” and [checkbox] = false then
me.Organization .BackColor = vbRed
End if
End sub
I forgot to mention that the [Organization] [Status] is amended by another form using a combo box, the operator has three selections, "Good", "Bad" or "Blank".

 

[JHB]

You can't use the code you show in a continuous form, then changing the back color reflect all records, not only a single one.
You can create rules using the "Conditional Formatting Rules Manager", which does what you want.
For further help post your database with some sample data, zip it then you haven't post 10 post yet.

 

[doucet1963]

Thanks for your quick answer, I tried the "Conditional Formatting Rules Manager" and that did not work. Maybe I typed the wrong information.

As requested I added the file (ZIP) maybe you can look at it and get a better idea. The textbox that I want to highlight is the one where the text is in Blue once you go through the first form that select the date.

I want the ones that say good highlighted in Yellow

And Maybe it is impossible to highlight a textbox based on a table in a continuous form.

I am no expert.

Cheers.

 

[JHB]

Try it now:

 

[doucet1963]

Sorry JHB for the time you spent on this but this is not really what I was looking for. The word "Good and Bad" in the textbox of an organization should have no impact on the formatting (example, Fuji, (Good)) because another table indicates the status.

The conditional formatting must act based from the table named Tbl_Accreditation_Documents. That table records the actual "Good" or "Bad" organization, the format should depend on two conditions, "Good" and "Uncheck" checkbox.

When the Organization moves from "good" move to "bad", or vice versa, the textbox should format accordingly. Like I said previously there may be no solution to this problem.

So I found out that it was easier to create a VBA for a sub-form that affects the form tracking the status of the company using a checkbox. True or false (good or bad).

Thanks again.