Programtically Setting Conditional Formatting For a Form In Access 2007

I have a number of sub forms that are displayed in datasheet mode. I need to set the background
color of columns. Unfortunately, if you just do that in design mode it is ignored in datasheet
mode.

However, conditional formatting is not ignored in datasheet mode. You can effectively set the
background column of a datasheet to a value by setting the conditional formatting to expression; the
expression to "1=1" and then the background to whatever you want.

The problem is I need to be able to do it at runtime. I opened the form I want to change in design
mode -- hidden, deleted any conditional formatting they had and tried to set the conditional
formatting for the controls I need to change where the problem arises.

To open the form in design mode:
DoCmd.OpenForm rs.Fields("FormName").Value, acDesign, , , , acHidden
Set frm = Forms(rs.Fields("FormName").Value)
I then walk the form's controls collection looking for the controls to change.

The following code is after I have found the control to change. First I make sure it is a textbox:
If (acTextBox ctl.ControlType) Then
Exit Do
End If
Then I delete any conditional formatting it may have had:
On Error Resume Next
Do While (ctl.formatconditons.Count 0)
ctl.FormatConditions.Item(0).Delete
If (0 Err.Number) Then
Exit Do
End If
ctl.FormatConditions.Refresh
Loop
On Error GoTo 0
Finally I try to add conditional formatting:
ctl.formatconditons.Add acExpression, , "1=1"
ctl.formatconditons.Item(0).BackColor = &H808080
The first line above throws an error: "Object does not support the property or method".

The question is "How do you add conditional formatting via VBA to controls in a form?"

Suggestions anyone?

Try this (untested)

ctl.formatconditons.Add acExpression, acNotBetween, 0, 0

Peter De Baets
http://www.peterssoftware.com

I actually did something similar and got it working. I used:
ctl.FormatConditions.Add acExpression, , "1=1"
Should have posted it earlier.


"Peter's Software" wrote:

Try this (untested)

ctl.formatconditons.Add acExpression, acNotBetween, 0, 0

Peter De Baets
http://www.peterssoftware.com