Iterate through records, assign color

[JustinP]

I am iterating through records in a subform using the "On current"
event for the subform to change the color of a textbox based on whether
it is redundant or not. Can anyone spot what I've done wrong?

Private Sub Form_Current()

With Me.RecordsetClone
If Not (.BOF And .EOF) Then
.MoveFirst
While Not .EOF
If Me.chkRedundant.Value = True Then
txtGoToPR.BackColor = RGB(64, 128, 128)
End If
.MoveNext
Wend
End If
End With

End Sub

 

[JustinP]

Just realised I should probably have mentioned what is going wrong. The
text box doesn't change color where the record is redundant. However,
when I click on a text box that has a record marked as redundant all
the text boxes for the all the records change color.

 

[CompGeek78]

Can you tell us more? What problem is occuring? Are you getting an
error message, are the not changing colors?

I'm not sure I understand what you are trying to accomplish. If you
are trying to change the color of the textbox based on whether or not
the checkbox is checked for that record, why don't you just use
conditional formatting?

Or are you trying to see if any data in the GotoPR field is redundant?
If so, and you are storing the answer to that question in the check
box, what code are you using to set the checkbox.


Keven

 

[JustinP]

Hi,

I have a form and subform. On the main form the user chooses an owner
name and the data in the form and subform updates. The subform shows
the properties owned by that owner that are currently affected by the
project. Some of these properties are no longer affected and are
flagged as "redundant" using a checkbox in another form and stored in a
table (the table the subform is querying). I need the user to be able
to see which properties (records) in the subform are still affected and
ones which are not.

I am trying to color those textboxes in the subform that are affected.

 

[CompGeek78]

The easiest way to do this is by using conditional formatting, as long
as your version of Access supports it.

If you select the text box you are trying to color, you should go to
Format, Conditional Formatting (if your version of Access supports
this).

In the first drop down box in that window (currently says field value
is), choose expression is. Then in the box to the right, type in
[chkRedundant] = true. Then below that, set whatever formatting you
want.

If your version of Access does not support conditional formatting, let
me know and I'll write up the VBA code to accomplish this.

Keven

 

[JustinP]

You are right that does work. Thanks.

Another question, is it possible to assign different colors to values
in a combo box based on the same sort of conditions?

The easiest way to do this is by using conditional formatting, as long
as your version of Access supports it.

If you select the text box you are trying to color, you should go to
Format, Conditional Formatting (if your version of Access supports
this).

In the first drop down box in that window (currently says field value
is), choose expression is. Then in the box to the right, type in
[chkRedundant] = true. Then below that, set whatever formatting you
want.

If your version of Access does not support conditional formatting, let
me know and I'll write up the VBA code to accomplish this.

Keven

Hi,

I have a form and subform. On the main form the user chooses an owner
name and the data in the form and subform updates. The subform shows
the properties owned by that owner that are currently affected by the
project. Some of these properties are no longer affected and are
flagged as "redundant" using a checkbox in another form and stored in a
table (the table the subform is querying). I need the user to be able
to see which properties (records) in the subform are still affected and
ones which are not.

I am trying to color those textboxes in the subform that are affected.