New Formatting Mystery

[Bill]

Arrrrgh!

In trying to extend the use of conditional formatting
with the addition of a "second" condition, either syntax
is "killing me" or one can't use variables defined in the
code sheet of the form. I.e., my code-sheet defines
a binary switch that under certain conditions will be
set TRUE and cause the conditional formatting to
evaluate to TRUE. The code-sheet variable is:

Dim GAP_On As Boolean

and the expression I added as the second condition
in the conditional formatting is simply:

[GAP_On] = True

The first condition is based on a table field value that is
included in the RecordSource query and that works
fine. However, it seems to ignore the second condition.

I only want the formatting to change when:

[GAP_Perf] <> "" AND [GAP_On] = True

BTW, is it only in the second condition portion of the
conditional formatting dialog that one defines the alternate
format?

Thanks,
Bill

 

[Marshall Barton]

In trying to extend the use of conditional formatting
with the addition of a "second" condition, either syntax
is "killing me" or one can't use variables defined in the
code sheet of the form. I.e., my code-sheet defines
a binary switch that under certain conditions will be
set TRUE and cause the conditional formatting to
evaluate to TRUE. The code-sheet variable is:

Dim GAP_On As Boolean

and the expression I added as the second condition
in the conditional formatting is simply:

[GAP_On] = True

The first condition is based on a table field value that is
included in the RecordSource query and that works
fine. However, it seems to ignore the second condition.

I only want the formatting to change when:

[GAP_Perf] <> "" AND [GAP_On] = True

BTW, is it only in the second condition portion of the
conditional formatting dialog that one defines the alternate
format?

You're right, you can not refer to VBA variables outside of
a VBA module. However, the one thing that you can refer to
just about anywhere in Access is a Public Function in a
standard module. This means that you can do what you want
by creating the simple function:

Public Function IsGapOn()
IsGapOn = GAP_On
End Function

Then use the CF expression:

[GAP_Perf] <> "" AND IsGapOn()

And, BTW, the first set of formating in the CF list is the
one that's used when none of the other three formats are
used. The second one is the first one that's checked, if
that one is false, then Access tries the second, and if the
second condition fails, then the third one is checked.

 

[Bill]

I wondered about using a public function and really
can't answer the question "why didn't I try that before
I bothered to post".

Anyway, that worked like a champ, so thanks Marsh
for the thought and suggestion.

Bill

In trying to extend the use of conditional formatting
with the addition of a "second" condition, either syntax
is "killing me" or one can't use variables defined in the
code sheet of the form. I.e., my code-sheet defines
a binary switch that under certain conditions will be
set TRUE and cause the conditional formatting to
evaluate to TRUE. The code-sheet variable is:

Dim GAP_On As Boolean

and the expression I added as the second condition
in the conditional formatting is simply:

[GAP_On] = True

The first condition is based on a table field value that is
included in the RecordSource query and that works
fine. However, it seems to ignore the second condition.

I only want the formatting to change when:

[GAP_Perf] <> "" AND [GAP_On] = True

BTW, is it only in the second condition portion of the
conditional formatting dialog that one defines the alternate
format?

You're right, you can not refer to VBA variables outside of
a VBA module. However, the one thing that you can refer to
just about anywhere in Access is a Public Function in a
standard module. This means that you can do what you want
by creating the simple function:

Public Function IsGapOn()
IsGapOn = GAP_On
End Function

Then use the CF expression:

[GAP_Perf] <> "" AND IsGapOn()

And, BTW, the first set of formating in the CF list is the
one that's used when none of the other three formats are
used. The second one is the first one that's checked, if
that one is false, then Access tries the second, and if the
second condition fails, then the third one is checked.