Conditionally Formatting Rows in a DataSheet Nested in a Form

[aedwards via AccessMonster.com]

Hi,

I've got a form with a subform in it, in DataSheet mode, that shows related
records from a different table.

I'd like to be able to apply conditional formatting to the rows in subform.
So I could set the background color of a particular row to red, based on a
value within that record. I would set the row's background to red, in effect,
by setting all the controls in the form (which as you will recall is embedded
as a subform inside a main form) to red.

I tried wiring to the Form_Current method of the form which serves as the
subform, but was surprised to not see it called once for every row that
appears in the datasheet. Instead it seems to always fire twice, regardless
of how many rows or related data there are. And the controls in all the
records, not just the ones that meet the specified criteria, are set with a
red background. Here is my code...

Private Sub Form_Current()
If Me.booDisplayInMainForm = False Then
Me.txtDate.BackColor = 255
Else
Me.txtDate.BackColor = 16777215
End Sub

 

[Marshall Barton]

I've got a form with a subform in it, in DataSheet mode, that shows related
records from a different table.

I'd like to be able to apply conditional formatting to the rows in subform.
So I could set the background color of a particular row to red, based on a
value within that record. I would set the row's background to red, in effect,
by setting all the controls in the form (which as you will recall is embedded
as a subform inside a main form) to red.

I tried wiring to the Form_Current method of the form which serves as the
subform, but was surprised to not see it called once for every row that
appears in the datasheet. Instead it seems to always fire twice, regardless
of how many rows or related data there are. And the controls in all the
records, not just the ones that meet the specified criteria, are set with a
red background. Here is my code...

Private Sub Form_Current()
If Me.booDisplayInMainForm = False Then
Me.txtDate.BackColor = 255
Else
Me.txtDate.BackColor = 16777215
End Sub

You can not use code to manipulate control properties in a
continuous or datasheet form. There is only one control
that is displayed multiple times so any property applies to
all the copies of the control.

Instead of using code, use the Conditional Formatting
feature (Format menu). In this case, I think you want to
use the Expression is option with the expression:
Not [booDisplayInMainForm]

 

[aedwards via AccessMonster.com]

Thanks Marshall. That did the trick!

Aaron

I've got a form with a subform in it, in DataSheet mode, that shows related
records from a different table.

[quoted text clipped - 18 lines]

Me.txtDate.BackColor = 16777215
End Sub

You can not use code to manipulate control properties in a
continuous or datasheet form. There is only one control
that is displayed multiple times so any property applies to
all the copies of the control.

Instead of using code, use the Conditional Formatting
feature (Format menu). In this case, I think you want to
use the Expression is option with the expression:
Not [booDisplayInMainForm]