Continuous forms controls - Conditional Formatting (1 Viewer)

[ryand09]

Hello

I’m trying to set the Backcolor of a control using a Conditional Formatting Expression. In the expression, the idea is to set the Backcolor based on a colour value in another table

I’ve got the following code in the expression…

[cboFilter].[BackColor]=DLookUp("[Colour_Code]","[tblName]","[ID]=" & [txtID])

This just isn’t working. I’ve tried lots of things just to try to get the colour to change e.g. ….

[cboFilter].[BackColor]=vbRed
[cboFilter].[BackColor]=”RED”
[cboFilter].[BackColor]=RGB(255, 224, 244)

But nothing seems to work. (I'm reaching the pulling my hair out stage) Any help very much appreciated.

If this backcolor cannot be changes this way, can it be done using VBA?

Thanks in Advance

 

[sneuberg]

This can be very frustrating as there are no error messages to tell you what's wrong. You just have to poke it with a stick until it works. One thing I occasionally forget to do is set the actually formatting. Below the area where you enter the expression you have to select a foreground color, background, color or you can disable the control. I suggest you start by enter the expression that's trivally true, e.g., 1=1 just to see some formatting going on and work your way from there.

If you can upload your database or a portion of it I'd be glad to try to figure out what is wrong.

 

[ryand09]

sneuberg

Thanks for getting back to me. I left this for a couple of days in the hope, I would see something. but I'm totally stumped here.

I've uploaded the tables and a test form. I've put the field on the form twice. Once with Field Value Is conditioning and one with (an attempted) Expression Is conditioning, using a DlookUp to try format the backcolor of the control

Any advice here is much appreciated.
Many thanks

 

[sneuberg]

It is very difficult to determine what you are trying to accomplish. What you have doesn't make much sense to me. Nonetheless I tracked down what was going on by putting a button on the form that displays both sides of the expression your have, displaying the DLookup follow by the background color in a message box. I changed the values in the table forcing them to match and still nothing was happening and then I found that no formatting had been applied. I gave them a red forecolor. You can see all of this in the attached database.

I hope this helps, but I think I could lend more help if you could tell me what your end goal is. Using this Dlookup can be slow and if I understood what you are trying to do I think I might be able to give you some alternatives.

 

[ryand09]

Once again, thanks for the follow up. Hopefully this explanation is a little clearer …


I’m trying to set dynamically the back color of a control (called Status_ID) on a continuous form. The control can have a number of values, Completed, Failed etc. Each value has a corresponding colour, stored (as a long Integer) in a table. I want to lookup that colour and apply it based on the control value.


I’m currently doing this manually on the continuous form using Field Value Is Equal to. I can continue to do it this way, it will just mean a lot of changes to form ad reports if another Status is added.

Alternatively can this be done using VBA?

Regards/

 

[sneuberg]

I think I understand. Since this status applies to numerous related tables that have numerous forms and reports, you want to be able to define the conditional formatting rules just once. Sorry but I don't have a clue. Maybe one of the other forum members can come up with something.

You can set the background color in VBA. In the attached database I set the background color of the Ctrl_Matrix_Status_ID_E in the form's current event with the Dlookup you had in the conditional formatting expression. This just acts weird. Depending on what record you click on all of backgrounds of all the records are set. So there's no question about whether you can set the background, but where would you do this to get the effect you are looking for.

It's too bad these formatting rules didn't form an object you could get a handle on. Even if you could copy and paste them all in one swipe, it would be a help.

Sorry that I couldn't help.

 

[JHB]

..
Alternatively can this be done using VBA?

Have a look in the link.
http://blogs.msdn.com/b/frice/archive/2004/06/08/151178.aspx

 

[sneuberg]

I think JHB's link is the answer. There is also https://msdn.microsoft.com/en-us/library/office/ff845092.aspx for more general information on this.

I tried out some of this code in the form load in the attached database. The code adds formatting rules to the Ctrl_Matrix_Status_ID_E combo box. It adds background colors that you have in the tbl_Control_Matrix_Status table. I just copied and pasted them into the code, but I'm sure you could set them by using Dlookup if you want. The main thing is that with using this collection you can do pretty much anything you want.