Record-wise conditional formatting

[Guest]

Hello All,

I have a pretty basic report based on a table of job numbers and fields like open date, gross, net, etc. I want to do something like display in bold an entire record based on a on whether a date has been entered in the "closed date" field. Format|Conditional Formatting only seems to allow to change the formatting of THAT field based on some criterion. My needs are to change the display of an entire record based on the value of a field within that record.

Can I do this without VBA or Macro?

Help!!

 

[fredg]

Hello All,

I have a pretty basic report based on a table of job numbers and
fields like open date, gross, net, etc. I want to do something like
display in bold an entire record based on a on whether a date has
been entered in the "closed date" field. Format|Conditional
Formatting only seems to allow to change the formatting of THAT
field based on some criterion.

This is not true.
You can conditional format any control other than the one used for the
criteria.
Select the Control you wish to change format of.
Let's say you wish to change the [Control1] formatting, based upon the
value of [Control5].
Select Control1.
Click Format + Conditional Formatting
Set it's Condition1 format to
Expression Is
Enter an expression in the dialog box:
[Control5] = "Cat"
Select the format for the control.
Save the change.
Do the same for all of the other controls of that record.

My needs are to change the display
of an entire record based on the value of a field within that
record.

Can I do this without VBA or Macro?

You can also do this in the Report section's (Detail?) Format event,
but that will require some simple coding... which you don't want to
do.

 

[Guest]

OK, it works!! And I really don't need to bold the labels.

After doing it I see that some code would be more efficient and malleable. I am going try a few things, but would love to see your idea.

Thanks a million.

I am going to try the non-coding way right now. However, I wouldn't be too adverse to a coding tip, since I am moving in that direction in my self-study.

If not too much trouble, please post your coding tip and I'll report back on how the menu-level conditional formatting worked.

One concern on the menu-level style: So far, seems that the control labels can't be manipulated via Format|Conditional Formatting. But I'll click around and see if there is something else.

Hello All,

I have a pretty basic report based on a table of job numbers and
fields like open date, gross, net, etc. I want to do something like
display in bold an entire record based on a on whether a date has
been entered in the "closed date" field. Format|Conditional
Formatting only seems to allow to change the formatting of THAT
field based on some criterion.

This is not true.
You can conditional format any control other than the one used for the
criteria.
Select the Control you wish to change format of.
Let's say you wish to change the [Control1] formatting, based upon the
value of [Control5].
Select Control1.
Click Format + Conditional Formatting
Set it's Condition1 format to
Expression Is
Enter an expression in the dialog box:
[Control5] = "Cat"
Select the format for the control.
Save the change.
Do the same for all of the other controls of that record.

My needs are to change the display
of an entire record based on the value of a field within that
record.

Can I do this without VBA or Macro?

You can also do this in the Report section's (Detail?) Format event,
but that will require some simple coding... which you don't want to
do.

Help!!

 

[fredg]

I am going to try the non-coding way right now. However, I wouldn't be too adverse to a coding tip, since I am moving in that direction in my self-study.

If not too much trouble, please post your coding tip and I'll report back on how the menu-level conditional formatting worked.

One concern on the menu-level style: So far, seems that the control labels can't be manipulated via Format|Conditional Formatting. But I'll click around and see if there is something else.

Hello All,

I have a pretty basic report based on a table of job numbers and
fields like open date, gross, net, etc. I want to do something like
display in bold an entire record based on a on whether a date has
been entered in the "closed date" field. Format|Conditional
Formatting only seems to allow to change the formatting of THAT
field based on some criterion.

This is not true.
You can conditional format any control other than the one used for the
criteria.
Select the Control you wish to change format of.
Let's say you wish to change the [Control1] formatting, based upon the
value of [Control5].
Select Control1.
Click Format + Conditional Formatting
Set it's Condition1 format to
Expression Is
Enter an expression in the dialog box:
[Control5] = "Cat"
Select the format for the control.
Save the change.
Do the same for all of the other controls of that record.

My needs are to change the display
of an entire record based on the value of a field within that
record.

Can I do this without VBA or Macro?

You can also do this in the Report section's (Detail?) Format event,
but that will require some simple coding... which you don't want to
do.

Help!!

Labels do not have a conditional formatting capability.
You can change the labels to unbound text controls, setting their
control source to something like:
="This is a label"

Now you can conditionally format the control along with the others.

To do this using code, assuming the controls are all in the detail
section, you would code the detail Format event like this:

If [Control5] = "Cats" Then
[Control1] .ForeColor = vbRed
[Control2].foreColor = vbRed
etc.
Else
[Control1] .ForeColor = vbBlack
[Control2].foreColor = vbBlack
etc.
End If