Modifying Conditional Formatting of Access Text Box on Continuous Form from VBA

The short answer is "Yes, you can, but it won't work as you intend"  Here is a trival procedure to colorize a textbox if a condition is met:

Private Sub Form_Current()
 
   If Me![ContactID] > 9 Then
      Me![txtLastName].BackColor = vbRed
   Else
      Me![txtLastName].BackColor = vbWhite
   End If
   
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

On a continuous form, it makes txtLastName Red for all rows when you are on a qualifying row.  On this tyjpe of form, anything that is not determined by binding to a field in the record is the same on all rows.  There are some ways of getting around this using Conditional Formatting, but it only permits 3 conditions.  Here is a screen shot illustrating the above problem:

Thanks, Helen,
I currently am using the 3 allowed conditions but would like to add additional conditions of unique formatting.  The problem you describe above is exactly the problem I am trying to solve.

Thanks,

Craig Harrington

Did we ever confirm ... that in code, you can have more than 3 or not ?

mx

MX; I saw your earlier post: 06/29/09 01:21 PM, ID: 24739837  "Let us know if you *can* do more than 3, ok ?
but did not see any replies.

Regarding my intial statement " the function is never enteed at runtime.".  I was able call a VBA function within one conditional formatting condition.  Initially I simply added "=Func()" to the condition line which did not execute.  Later I modified the condition to "iif(Func(), TRUE,FALSE) and was successful in that Func() was indeed called.  Ultimately this procedure failed in that once again all records were affected.

Any other thoughts?

Thanks,

Craig Harrington

Unfortunately, I don't know of any way to get more than 3 conditions for CF forms.

Even with vba code CF model ?

mx

I placed the following code in Condition 1 of a text box in the detail section of a continuous form
                                  IIf(ConditionFormat()=True,True,False)

The following is a general function in the form code module.
Private Function ConditionFormat() As Boolean
    Dim lngRed As Long
    Dim lngBlue As Long

    lngRed = RGB(255, 0, 0)
    lngBlue = RGB(0, 0, 255)

    If Me.ActStatus < 0 Then
        Me.rowBackGrndColor.FormatConditions(0).BackColor = lngRed
    Else
        Me.rowBackGrndColor.FormatConditions(0).BackColor = lngBlue
    End If
    ConditionFormat = True
End Function

I placed a break in the ConditionFormat function and saw that is is being called from the forms' recordset.UpdateRecSource which I set explicitly in vba.

I thought I had been successful at adjusting the textbox's color in an earlier attempt but now Access crashes after exiting the ConditionFormat function.

Thanks for the help.

Craig Harrington

It would be possible to do a multi-condition function (say, using a Select Case statement), run from the Current event of a CF form, using a match to a combo box or textbox in the form header (as in my sample above), but it would only apply to the current record.  IF that is what you need, I will work up some code.

No thank you Helen, I appreciate your help.
CH

re:> Initially I simply added "=Func()" to the condition line which did not execute.

I have use function calls in CF with success. As long as you are not asking for more then three colors, for example, what you are asking may be possible. If so post a sample application here.

Mike

Three colors are no problem for conditional formatting.  In fact, I have implemented conditional formatting for three colors on a continuous form.  More, though, is very problematic -- maybe impossible.