Conditional format null values for all 200 fields in a form at once?

[HGCanada]

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]

Is this in a single or continuous form view? If single this could be done easily in code.

 

[Ranman256]

use 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

 

[HGCanada]

It's a single form view.

 

[MajP]

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

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]

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]

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 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]

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]

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]

Can only select textboxes and I think comboboxes. If you select other controls it will get grayed out.

 

[theDBguy]

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.

 

[Galaxiom]

I have a form with over 200 fields on it.

You probably should reconsider your data structure.

 

[MajP]

You probably should reconsider your data structure.

That was also my next comment.