Conditional Formatting w Multiple Conditions

[HK123]

Good afternoon,

I have a continous subform on a form where I need to use conditional formatting to change the colors of a combo box based on the value selected. The data type of the values are all short text.

Combo box drop down is [Item_ID] selections I want to affect are SWA, BH, FREIGHT (and a few others but you get the idea)



I know how to use conditional formatting a little bit. But I want to know if any of these codes will work, or how if I need to make individual rules for all of the selections I want to be affected.

These were my ideas, but I am not sure if the OR, or a , is how I should separate the values if either of these is right at all.

Expression is [Item_ID] = "SWA" OR "BH" OR "FREIGHT"

Expression is [Item_ID] = "SWA" , "BH" , "FREIGHT"


I am unable to make edits to the system at this time or I would just use trial and error.

Any advice is appreciated.

Thank you!

[June7]

Item_ID is a text field set as primary key and you save descriptive text values?

Expression is [Item_ID] = "SWA" OR [Item_ID] = "BH" OR [Item_ID] = "FREIGHT"

or

Expression is [Item_ID] IN ("SWA", "BH", "FREIGHT")

[CJ_London]

Neither of those is right. You could try

Expression is eval([Item_ID] & “ in ( ‘SWA’, ‘BH’ , ‘FREIGHT’)”)

note I am on my phone so the quotes will need correcting

[June7]

Both of my suggestions worked. Why would Eval() be needed?

[CJ_London]

In vba you cannot use ‘in’ unless you enclose it in eval. Was on my phone and couldn’t remember if a conditional format expression was in vba or sql

[HK123]

Sorry, totally forgot to mention I am using the conditional formatting from the ribbon in design view.

the name Item_ID is not the primary key, just the name of the field. Probably should have named it something else.

so the format

Expression is [Item_ID] = "SWA" OR [Item_ID] = "BH" OR [Item_ID] = "FREIGHT"

or

Expression is [Item_ID] IN ("SWA", "BH", "FREIGHT")


should work in the conditional formatting ribbon?

[CJ_London]

Why ask? Try it then report back what works and what doesn’t

edit @june, just realised you may be thinking I was commenting on your post - I was actually commenting on the original post

[HK123]

@CJ_London

Lol yes I know that would be the logical way to go about it.. however I went down a rabbit hole trying multiple ways to get it to work and didn't want to waste anymore time if I was totally off base or if it had to be done in another view than design. So many times I think I am on the right path and end up going down it way too far only to realize my mistake. Lol.. guess it is probably a good learning experience.

I am also not able to make edits at the moment. But once I am able to go in and make some changes I will test and repot back!

Thank you all for your help!

[CJ_London]

why can't you make edits at the moment? - sounds like your setup is wrong....

[June7]

Well, there is no place else to create Conditional Formatting rules except with VBA.

CJ, I figured you were addressing the original post but my reply was still pertinent. I tested expressions before posting suggestion. IN() worked for me without Eval().

[HK123]

Too many other users were in the system using the form to edit it at the time.

None of these suggestions seem to be working. I will keep trying different variations.

[Welshgasman]

Too many other users were in the system using the form to edit it at the time.

None of these suggestions seem to be working. I will keep trying different variations.

You should NOT even be even tryong to attempt to modify a production system.
You do it and test it on your development system. And when thoroughly tested and working.
, does it become the production system.
,

[June7]

What does "None of these suggestions seem to be working." mean - what happens?

What other variations could you try? I tested my suggestions and they worked.

[HK123]

Yikes, I did not know you could not make edits to an active system. I just thought nobody could be using it at the time it was being worked on. Now that I know I will only make edits on copies to ensure everything is working beforehand.

That being said I am not going to continue trying to change the format at this time.

Thank you all for your help.

[June7]

Well, can make edits to active system if you want but probably shouldn't and depends which file you meant. A multi-user database should be split and each user runs their own copy of frontend. Make edits to a master frontend anytime then publish it so users can replace their local copy. If you need to make edits to backend then yes, definitely wait until no one is using. Make sure to backup first.