[X]My Assistant

[freespiritcheris...]

Expression on multiple controls if value matches value of single control.on same form.

How do I conditional format the background of multiple controls based on the value of another another control on the same form?

Thanking you in advance.

Attached File(s)

 Capture.JPG ( 121.58K )Number of downloads: 9

 

[Hugoj]

You could try something like this

Private Sub Form_Current()
Dim lngYellow As Long, lngWhite As Long
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
If Me.txtBase.Value = Me.txtCheck1.Value Then
Me.txtCheck1.BackColor = lngYellow
Else
Me.txtCheck1.BackColor = lngWhite
End If
End Sub

Where txtBase is the Control you compare with. If this is what you are looking for you can create a loop for all the Controls you want to test.

[RJD]

Hi: Let'd make sure I understand your requirement: If ANY of the values in controls in a row (qa through qo) matches the value in control cboPIP, then color the background in ALL the controls on that row (qa through qo).

If this is correct, one way is to create another control to indicate that a match occurs ... for example, =IIf(qa=cboPip Or qb=cboPip Or ...... ,"x","") and call it, say, [Check]

Then sweep over the controls qa through qo and set the CF to Check = "x".

See the demo attached. Note that I put the Check logic on the form and made it invisible, but you could put it in a record source query instead. See if this matches your requirement or if we need further explanation...

HTH
Joe

Attached File(s)

 MultipleConditionalFormat.zip ( 25.15K )Number of downloads: 8

 

[arnelgp]

use Conditional Formatting.

select all textbox (excluding the PIP).
on the Ribbon->Format->Conditional Formatting.
"New Rule"->Field Value ->Equal To->[Pip]
choose the background.
and Apply.

[freespiritcheris...]

@RJD
@arnelgp

You guys are AMAZING!!!!!! Thank youuuuuu x
This post has been edited by freespiritcherishes: Oct 5 2019, 07:11 AM

Attached File(s)

 Capture.JPG ( 73.56K )Number of downloads: 1

 

[freespiritcheris...]

@RJD

This is so good RJD, thank you again. I was having a play and found that there is a limit to the number of 'Or's. I tried to take it to 50 fields in a query to compare with cbopip, and it worked to about 30 fields. I found it won't do anymore, is there a way round this limitation?

=IIf([qa]=[cbopip] Or [qb]=[cbopip] Or [qc]=[cbopip] Or [qd]=[cbopip] Or [qe]=[cbopip] Or [qf]=[cbopip] Or [qg]=[cbopip] Or [qh]=[cbopip] Or [qi]=[cbopip] Or [qj]=[cbopip] Or [qk]=[cbopip] Or [ql]=[cbopip] Or [qm]=[cbopip] Or [qn]=[cbopip] Or [qo]=[cbopip],[Pip],"").

Thank you in advance.
This post has been edited by freespiritcherishes: Oct 6 2019, 01:43 PM

[RJD]

Hi: Before we address your last concern, help me clear up something. My reading of your initial request led to the demo I posted. That demo colors the background of ALL controls on a row if ANY of them have a value matching the PIP value. However, in Post 5, you show ONLY the control with the matching with a colored background. So I need a clarification of the requirement.

If the requirement is just for the matching value, then the multiple ORs in the IIf statement is not required at all. If the requirement is to color ALL the controls, then we can address how to do that avoiding OR limits.

In either case, I wonder about the underlying data and whether you need to look at normalization issues as well.

Awaiting your guidance ...

HTH
Joe

[freespiritcheris...]

Hi Joe,

You are so kind, thank you for replying.

I'm so embarrassed and yet it is a great testimony to just how educational your assistance is. Just by the sample you posted, me playing about with it, discovering a limitation, asking you again, knocking together a quick sample of 50 fields to post to show my trouble only to find, that there is no trouble at all and 50 fields, has worked. I'll post it anyway, so that you can see what I was trying to get at but your help and that of UA is invaluable, can't thank you enough x

Attached File(s)

 mypip.zip ( 84.52K )Number of downloads: 8

 

[RJD]

Glad you got that solved. Actually, it was arnel's solution, CF one by one, that got you what you wanted. We interpreted the requirement differently (I for coloring all controls whereas his for selected), and he had the one that ended up being what you wanted.

But looks like you are good-to-go now ... and we are both happy to assist.

Regards,
Joe

[freespiritcheris...]

Hi arnelgp, Joe..

Is it possible to conditional format the background of Multiple Controls based the Value of TWO controls on the same form? I have needed to add a'gname' to the table in the sample. I need to highlight the matched number as before, but with a different colour person.. what expression can I use? It is currently this..

I've tried so many expression variations..
Thanks in advance.
This post has been edited by freespiritcherishes: Oct 18 2019, 06:57 PM

Attached File(s)

 Capture1.JPG ( 81.39K )Number of downloads: 0
 Capture2.JPG ( 174.8K )Number of downloads: 1
 Capture4.jpg ( 186.18K )Number of downloads: 2
 modpip.zip ( 77.41K )Number of downloads: 3

 

[RJD]

Hi: Well, this is a bit tedious, but do-able. Here is what I did to revise your db ...

For existing controls 1A - 50A, make background transparent.

Make 50 new textbox controls, with the following guidance ...
- make control values =[1A] & [gname], =[2A] & [gname], etc. to =[50A] & [gname]
- make border transparent
- arrange Send to Back
- apply rules, per the example in qry_demo1 (note that the fill and font are the same color)
- place the new controls behind the associated old controls 1-50

See qry_demo2 for the results in 14A
See qry_demo1 for how the CF rules are put together in the extra control.

You can add other rules for other names (note the catch-all that makes the background white if none of the conditions apply), or change colors as desired.

See if this is close to what you are trying to do. This is a lot of trouble, and you have to decide if it is worth it. I did only one control, so if you think this is worth it, you can work through the other 49 controls.

Note that after you create the 50 new controls, you can sweep over them, choose CF from the ribbon, and apply the rules to all controls. I do not know if any limits apply to this approach, but you should try.

HTH
Joe

Attached File(s)

 modpip_Rev1.zip ( 50.57K )Number of downloads: 7