Conditional format null values for all 200 fields in a form at once? (1 Viewer)

HGCanada

Registered User.
Local time
Today, 04:43
Joined
Dec 30, 2016
Messages
82
Hi. I have a form with over 200 fields on it.

I want to maintain the conditional formatting I've already got in there for specific values. I also want to add conditional formatting such that all the fields are greyed out when a new record is created, and they only turn white once data are entered. Is there a way that I can do this for all the fields on the form simultaneously, without having to code each one?

Thanks in advance.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
Is this in a single or continuous form view? If single this could be done easily in code.
 

Ranman256

Well-known member
Local time
Today, 04:43
Joined
Apr 9, 2015
Messages
4,337
use code:
Code:
sub form_Current()
  ColorBoxes
end sub

sub form_afterupdate()
  ColorBoxes
end sub

sub ColorBoxes
dim ctl as control
const kGRAY = 15132391

for each ctl in controls
    if me. newrecord then
      ctl.backcolor = kGRAY
   else
      ctl.backcolor = vbWhite
   endif
next
set ctl = nothing
end sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
Code:
for each ctl in controls
    if me. newrecord then
      ctl.backcolor = kGRAY
   else
      ctl.backcolor = vbWhite
   endif
next
The above code may error because not all controls have a backcolor property, also you may not want all controls. So Tag the ones you want
Code:
for each ctl in me.controls
 if ctl.tag = "YourTagHere" then 
 if me. newrecord then
      ctl.backcolor = kGRAY
   else
      ctl.backcolor = vbWhite
   endif
end if
next
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:43
Joined
Oct 29, 2018
Messages
21,357
Hi. I have a form with over 200 fields on it.

I want to maintain the conditional formatting I've already got in there for specific values. I also want to add conditional formatting such that all the fields are greyed out when a new record is created, and they only turn white once data are entered. Is there a way that I can do this for all the fields on the form simultaneously, without having to code each one?

Thanks in advance.
Hi. If you're asking how to apply the same CF rule to multiple controls at the same time, then just select them all at once first before creating the CF.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
Hi. If you're asking how to apply the same CF rule to multiple controls at the same time, then just select them all at once first before creating the CF.
I think the expression would still have to be unique to check if that control is null or has a value. That was the way I interpreted it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:43
Joined
Oct 29, 2018
Messages
21,357
I think the expression would still have to be unique to check if that control is null or has a value. That was the way I interpreted it.
Oh, I see. For Null Values, though, it's still possible to apply what I said, only, but it would be the opposite. For example, let's say you want to highlight all empty textboxes in yellow. Using the approach I am suggesting, you will have to set the BackColor of all textboxes to yellow and use CF to change the BackColor to white if the control is not empty. Cheers!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
Oh, I see. For Null Values, though, it's still possible to apply what I said, only, but it would be the opposite. For example, let's say you want to highlight all empty textboxes in yellow. Using the approach I am suggesting, you will have to set the BackColor of all textboxes to yellow and use CF to change the BackColor to white if the control is not empty. Cheers!
I was thinking an expression, but forgot about just checking the value. So I guess you can simply do it all at once.
 

HGCanada

Registered User.
Local time
Today, 04:43
Joined
Dec 30, 2016
Messages
82
Thanks. It doesn't seem possible to conditionally format all of them at once (I select them all, right click, and then there is no conditional formatting option on the menu that comes up).

I'm a bit lost - not very familiar with coding or tags. Where do I enter this code, so that it applies to all of the fields on my entire form at once?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
Can only select textboxes and I think comboboxes. If you select other controls it will get grayed out.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:43
Joined
Oct 29, 2018
Messages
21,357
Thanks. It doesn't seem possible to conditionally format all of them at once (I select them all, right click, and then there is no conditional formatting option on the menu that comes up).

I'm a bit lost - not very familiar with coding or tags. Where do I enter this code, so that it applies to all of the fields on my entire form at once?
Hi. Rather than right-click, select all controls that can accept CF and then go to the Format Ribbon instead.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:43
Joined
May 21, 2018
Messages
8,463
You probably should reconsider your data structure.
That was also my next comment.
 

Users who are viewing this thread

Top Bottom