UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Do I Conditional Format The Background Of Multiple Controls Based On The Value Of Another Control On The Same Form?, Access 2013    
 
   
freespiritcheris...
post Sep 30 2019, 09:03 AM
Post#1



Posts: 155
Joined: 19-October 04



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 hat_tip.gif in advance.
Attached File(s)
Attached File  Capture.JPG ( 121.58K )Number of downloads: 9
 
Go to the top of the page
 
Hugoj
post Sep 30 2019, 09:49 AM
Post#2



Posts: 35
Joined: 22-August 10



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.

Go to the top of the page
 
RJD
post Sep 30 2019, 09:50 AM
Post#3


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


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)
Attached File  MultipleConditionalFormat.zip ( 25.15K )Number of downloads: 8
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
arnelgp
post Sep 30 2019, 10:13 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


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.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
freespiritcheris...
post Oct 5 2019, 07:03 AM
Post#5



Posts: 155
Joined: 19-October 04



@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)
Attached File  Capture.JPG ( 73.56K )Number of downloads: 1
 
Go to the top of the page
 
freespiritcheris...
post Oct 6 2019, 01:42 PM
Post#6



Posts: 155
Joined: 19-October 04



@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
Go to the top of the page
 
RJD
post Oct 6 2019, 02:42 PM
Post#7


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


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




--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
freespiritcheris...
post Oct 6 2019, 05:02 PM
Post#8



Posts: 155
Joined: 19-October 04



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)
Attached File  mypip.zip ( 84.52K )Number of downloads: 8
 
Go to the top of the page
 
RJD
post Oct 6 2019, 06:02 PM
Post#9


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
freespiritcheris...
post Oct 18 2019, 06:52 PM
Post#10



Posts: 155
Joined: 19-October 04



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.. crazy.gif
Thanks in advance.
This post has been edited by freespiritcherishes: Oct 18 2019, 06:57 PM
Attached File(s)
Attached File  Capture1.JPG ( 81.39K )Number of downloads: 0
Attached File  Capture2.JPG ( 174.8K )Number of downloads: 1
Attached File  Capture4.jpg ( 186.18K )Number of downloads: 2
Attached File  modpip.zip ( 77.41K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Oct 19 2019, 11:26 AM
Post#11


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


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)
Attached File  modpip_Rev1.zip ( 50.57K )Number of downloads: 7
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 04:38 AM