Conditional formatting based on the Row Source in another control.

  • Thread starter Thread starter Aria
  • Start date Start date
A

Aria

How do you conditionally format a text box that is based on an IIF statement
in another control?

This is a sub form (sfrmKeys) with the following SQL:


Row Source: SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblWings].[WingDescription], [tblBuildings].[BuildingDescription],
[tblBuildings].[Active], IIf([Active]=False,[BuildingDescription] & "
(Ret.)",[BuildingDescription]) AS [Retired Bldgs] FROM tblBuildings INNER
JOIN (tblWings RIGHT JOIN tblLocks ON
[tblWings].[WingID]=[tblLocks].[WingID]) ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID] ORDER BY
[tblLocks].[Location];

The Control Source of txtBuilding is:
=[cboSelectLock].Column(5)


I didn’t account for bldgs being retired so I’m trying to fix everything
now. What I would like to happen is that only “(Ret.)†turns red if the bldg
is retired. Since the Row Source is for cboSelectLock, I didn’t know how to
get it to do what I want for txtBuilding.
 
Use conditional formatting
Change the Field Value is to Expression is
Enter the following as the expression
[Name of the Text Box] = "(Ret.)"
And set up the format


If that fails try
[cboSelectLock].Column(5) = "(Ret.)"
as the expression


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Unfortunately, neither method worked. There wasn't any change. I did change
it to "Expression is". I don't know if I was clear in my previous post but
just in case it makes a difference there is a building description in front
of "Ret.". For example, it may say Technology, (Ret.)
--
Aria W.


John Spencer said:
Use conditional formatting
Change the Field Value is to Expression is
Enter the following as the expression
[Name of the Text Box] = "(Ret.)"
And set up the format


If that fails try
[cboSelectLock].Column(5) = "(Ret.)"
as the expression


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How do you conditionally format a text box that is based on an IIF statement
in another control?

This is a sub form (sfrmKeys) with the following SQL:


Row Source: SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblWings].[WingDescription], [tblBuildings].[BuildingDescription],
[tblBuildings].[Active], IIf([Active]=False,[BuildingDescription] & "
(Ret.)",[BuildingDescription]) AS [Retired Bldgs] FROM tblBuildings INNER
JOIN (tblWings RIGHT JOIN tblLocks ON
[tblWings].[WingID]=[tblLocks].[WingID]) ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID] ORDER BY
[tblLocks].[Location];

The Control Source of txtBuilding is:
=[cboSelectLock].Column(5)


I didn’t account for bldgs being retired so I’m trying to fix everything
now. What I would like to happen is that only “(Ret.)†turns red if the bldg
is retired. Since the Row Source is for cboSelectLock, I didn’t know how to
get it to do what I want for txtBuilding.
Click to expand...
Click to expand...
 
Then you need to edit the expression to
[Name of the Text Box] LIKE "*(Ret.)"

Which says if the value ends in (Ret.) then apply the conditional
formatting. If there is anything after (Ret.) then you will need to add
another asterisk after the closing parenthesis. Then the expression
will be true if (Ret.) is anywhere in the value.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Well, this is the closest I've gotten so far. What's happening is that
everything inside the text box is red and bold. I guess I could live with it
if there isn't another way but I would really like just (Ret.) to have the
formatting.
This is what I put:
Expression is [txtBuilding] LIKE "*(Ret.)"
--
Aria W.


John Spencer said:
Then you need to edit the expression to
[Name of the Text Box] LIKE "*(Ret.)"

Which says if the value ends in (Ret.) then apply the conditional
formatting. If there is anything after (Ret.) then you will need to add
another asterisk after the closing parenthesis. Then the expression
will be true if (Ret.) is anywhere in the value.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Unfortunately, neither method worked. There wasn't any change. I did change
it to "Expression is". I don't know if I was clear in my previous post but
just in case it makes a difference there is a building description in front
of "Ret.". For example, it may say Technology, (Ret.)
Click to expand...
Click to expand...
 
No, conditional formatting will be applied to the entire contents of the source.

If you are working with Access 2007 and use rich text you can probably use
some VBA to highlight just the portion you want. Personally, I wouldn't
bother as it feels like too much trouble.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
OK, I guess I'm done then. :-)
Thank you very much, John.
--
Aria W.


John Spencer MVP said:
No, conditional formatting will be applied to the entire contents of the source.

If you are working with Access 2007 and use rich text you can probably use
some VBA to highlight just the portion you want. Personally, I wouldn't
bother as it feels like too much trouble.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Well, this is the closest I've gotten so far. What's happening is that
everything inside the text box is red and bold. I guess I could live with it
if there isn't another way but I would really like just (Ret.) to have the
formatting.
This is what I put:
Expression is [txtBuilding] LIKE "*(Ret.)"
Click to expand...
Click to expand...
 
Back
Top