Change color of a control on a Continuous Form

[chuckcoleman]

On a Continuous Form, can you change the color of a control based on criteria? I know using how to change the color of a control using VBA and the OnCurrent event, but the real question is when the form is displayed with multiple rows for each record, can the control color for row number 1 be red but the control color for row number 2 be black? (The criteria sets the color.) That is the behavior I would like to see; I just don't know if you can do this. I'm guessing that a record in a Continuous Form becomes "current" only when you click on a row. Any advice?

Chuck

 

[boblarson]

What control are you wanting to change the color of? If it is a text box, you can use Conditional Formatting.

 

[chuckcoleman]

Bob, it's a Command Button. When you click on the Command Button it opens an Order Form. I want to change the Command Button so the text is red if there is an existing order or black in there are no orders. I have the logic to determine if there are orders, I'm just trying to figure out on a Continuous Form that has the Command Button visible on each row if I can change the text color based on the logic if there is an existing order, (for each row).

Chuck

 

[gemma-the-husky]

you cant change the text colour for a single button directly. you might be able to use conditional formatting, though.

 

[missinglinq]

Conditional Formatting isn't available for Command Buttons. As a workaround, however, you could replace the Command Button with a Textbox, use its OnClick event and format it using Conditional Formatting.

Linw ;0)>

 

[boblarson]

You can also keep the command button there but make it transparent using its transparent property set to yes and sitting over the text box.

 

[chuckcoleman]

Bob, what do you mean "sitting over the text box"? You also said you can keep the command button but followed it up with the text box comment. I'll wait for a reply and try a few other things. Thanks all.

 

[boblarson]

Bob, what do you mean "sitting over the text box"? You also said you can keep the command button but followed it up with the text box comment. I'll wait for a reply and try a few other things. Thanks all.

What I mean is

You use a text box to display the color changes and format it to look as much like a button as possible. Then you place your command button over the top of the text box. If you set the command button's TRANSPARENT property to YES, the command button becomes transparent (can't see it) but you can still click on it and the click event of it still works.

 

[chuckcoleman]

Bob, thank you for clarifying. I'll try it shortly. I still have a question in my mind on a continuous form if each record could display a control with a different color based on some criteria. I hope I'm wrong. I'll report back tomorrow. Thank you again.

 

[Galaxiom]

What is the advantage of using a transparent command button over the top of a textbox rather than the OnClick event of the textbox itself?

 

[boblarson]

What is the advantage of using a transparent command button over the top of a textbox rather than the OnClick event of the textbox itself?

In this case, just that you wouldn't need to move the code from the current location and that if you have the text box formatted to look like a button, you still need to have the locked property set to yes (if you don't want changes to the caption which is the text of the text box) and in order for the user to not have it highlight the text or have the cursor inside of the text box you would need to set the enabled property to NO which then renders it useless as the click event isn't recognized then.

 

[SmallTime]

I've been using something similar to Bob's suggestion for a while and it works really well, just don't forget to disable the tab stop for the underlying textbox otherwise the illusion might be rumbled.

SmallTime

 

[Galaxiom]

I asked about the advantages because I stumbled on an interesting alternative while setting up a structure like Bob already described. (Just "another way to skin a cat" as Linq puts it.)

if you have the text box formatted to look like a button, you still need to have the locked property set to yes (if you don't want changes to the caption which is the text of the text box) and in order for the user to not have it highlight the text or have the cursor inside of the text box you would need to set the enabled property to NO which then renders it useless as the click event isn't recognized then.

Locking is obvious since it is intended to act as a button and we don't want the "caption" edited.

However the Enabled property needs further attention.

An interesting ability of using the textbox directly as a simulated button is being able to actually enable it according to the Conditional Formatting. The Enabled property is set to No and the Conditional Format set to enable it on condition by ticking the Enabled box in the formatting setup.
(BTW. In VBA this is the FormatConditions(x).Enabled property)

The overlayed technique can only use the Conditional Format on the textbox to make it look as though the button is disabled. Of course the procedure can also incorporate the code to subvert the action but that requires extra code to to test the intended Enabled state of the button.

However, as Bob pointed out, avoiding the focus is a real issue because that would give the game away.

When the simulated button is disabled it cannot receive focus. To prevent the highlight of the "caption" while it is enabled, simply move the focus somewhere else in the click procedure. (Also the double click procedure if you are really concerned about the user finding a way move into the "button".

If the button, say, opens another form then the deception is complete without a hint of having had to move focus. Even if we stay on the same form, a button often does something that can more or less logically justify moving the focus to another control.

I find that this minor fiddle with the focus is more than compensated by saving one extra control (particularly the hassles with selecting controls that share the same location) and the saving of code to subvert the click when the button is supposed to be disabled.

 

[gemma-the-husky]

I use transparent command buttons from time to time. You can overlay graphic images as well with them - eg have buttons over each state of a US map.

OP

THe reason why you need a workround to get different effects for a continuous form, is that when you design a continous form you olny specifiy a single row, and therefore all the attributes of that row are duplicated in the other rows.

So how do you (or can you) arrange for one example of that row to have a yellow background, say, and all the others a white backgroiund. It isnt easy. MS added conditional formatting to help with this, I expect, but it is fiddly to use.

I recall ChrisO recently demonstrated a different mechanism to do this in another post which I cant locate.

But there are no straightforward methods to differentiate continous rows.

 

[Galaxiom]

So how do you (or can you) arrange for one example of that row to have a yellow background, say, and all the others a white backgroiund. It isnt easy. MS added conditional formatting to help with this, I expect, but it is fiddly to use.

I recall ChrisO recently demonstrated a different mechanism to do this in another post which I cant locate.

But there are no straightforward methods to differentiate continous rows.

There are the BackColor and AlternateBackColor properties of the Detail section of a form. This produces row banding behind the controls and works with Continuous Forms and Datasheet.

I haven't seen what ChrisO posted but when I was just starting out I recall a very complex technique done with some sort of transparent layer. With literally pages of code it was way over my head at the time but I think it was to do banding before the AlternateBackColor property was introduced.

FormatConditions includes a Type called FieldHasFocus used to change the BackColor on one "field" (grrr control) in the current record.

At least that is something but unfortunately Microsoft didn't think far enough and didn't include a mechanism to highlight the whole current record.