If statement effecting all records in continuous form

[vapid2323]

I want to create a highlighted effect behind my check boxes, to do that I added a bunch of boxes behind them, then added a line of code to check if the checkbox was true and change the box color as needed.

The issue is that it changes the entire colume of my continuous form when I only want it to effect the single record.

If ProductReturned = True Then BoxProductReturned.BackColor = vbRed

ProductReturned is the checkbox.
BoxProductReturned is the box behind it.

 

[pbaldy]

The simplest way to achieve this would be with Format/Conditional Formatting (on the ribbon starting in 2007).

 

[vapid2323]

The simplest way to achieve this would be with Format/Conditional Formatting (on the ribbon starting in 2007).

Thats what I wanted to do, but its not allowing it for a Box OR a Checkbox.

This was my second solution

 

[pbaldy]

It should work on a textbox, which is what I thought you meant by box. It won't work on the checkbox or a rectangle, if that's what you meant. Code based formatting is problematic on a continuous form. It's simple on a report, because the report has a detail format event that lets you affect each record individually.

 

[vapid2323]

It should work on a textbox, which is what I thought you meant by box. It won't work on the checkbox or a rectangle, if that's what you meant. Code based formatting is problematic on a continuous form. It's simple on a report, because the report has a detail format event that lets you affect each record individually.

Nice, texbox works just fine

 

[pbaldy]

Ah good, glad it worked for you.

 

[missinglinq]

Paul gave you the solution here, but FYI, in a Continuous or Datasheet View Form, formatting for one instance of a Control formats all instances of the Control (on all Records.)

The way around this, as Paul suggested, is to use Conditional Formatting. Unfortunately, Conditional Formatting choices are limited both in the type of Formatting and the number of Conditions that can be addressed. You're pretty much limited to Text and Back Color Formatting, and Enabling/Disabling a Control.

You can use the traditional approach to formatting, using code in the Form_Current event, in these types of Forms, as long as the formatting doesn't change the appearance of the Controls.

You could, for instance, Lock a Control via code in Form_Current. This will Lock or Unlock the Control on all Records, but since Locking doesn't alter the appearance of the Control, it doesn't matter. The Current Record will be formatted appropriately as you move from Record to Record.

Linq ;0)>