Programming Conditional Formatting using Multiple Criteria

[tcanis]

Hi,

I have a form in which I would like to format the background color of a
field control based on the criteria in another field control which contains
10 different criteria. I would like the background color to be distinct
based on the criteria choosen.

For example, The field control is called "code" and my criteria for that
field are 0, 1, 1.5, 2, 3, 3.5, 3.6, 3.7, 4 and 5. The field control I would
like to format is called "PR #". If "0" is choosen in the "Code" field, I
would like the background color of the "PR #" to be blue, if "1" is choosen I
would like the background color to be gray etc.

How do I do this?

 

[David H]

The following code dynamically creates format conditions for controls in a
subform. I developed the code as it opens up the color palette, however
you're still limited with the formatting. Also, as of Access 2003, you were
only allowed (3) format conditions so you might have to get creative as you
won't be able to have a formatcondition for each value. As you can tell, I
had major problems getting it just right and ended up splitting the form into
two separate subforms.

The code should be called from the Form_Load event.

Private Sub setFormatConditionsSubfrmUniversalLoadListDetail()
' Redesigned *AGAIN* to try and improve the implementation, the original
form which captured manifest quantity
' and loaded the trailer has been split into two different subfrms to make
the conditional formatting a bit easier
' to implement as well as the process of loading a trailer

On Error GoTo Err_setFormatConditionsSubfrmUniversalLoadListDetail

Dim i As Integer
Dim ctl As Object
Dim newCondition As Object
Dim strMsgText As String

'The FC's can be set via the tools menu, however the color options are
limited setting them via code
'opens up the full pallete; also it centralizes their setup allowing
changes to be made easier

'-------------------------------------------------------------------------------------------------------------------
'DELETE CONDITIONAL FORMATING ON THE MANIFEST DETAIL AND START FROM
SCRATCH EACH TIME THE FORM IS LOADED
For i = 0 To Me.subfrmUniversalLoadListDetail.Controls.Count - 1
Set ctl = Me.subfrmUniversalLoadListDetail.Controls(i)
With ctl
If .ControlType = acComboBox Or .ControlType = acTextBox Then
.FormatConditions.Delete
Debug.Print .Name, .FormatConditions.Count
End If
End With
Next i

'DISABLE THE CONTROL IF THE RECORD IF ITS SHOWING AS LOADED ONTO A TRAILER
For i = 0 To Me.subfrmUniversalLoadListDetail.Controls.Count - 1
Set ctl = Me.subfrmUniversalLoadListDetail.Controls(i)
With ctl
If .ControlType = acComboBox Or .ControlType = acTextBox Then
If .Tag Like "*" & "manifestDetail" & "*" Then
Set newCondition =
ctl.FormatConditions.Add(acExpression, ,
"Forms!frmLoadLists!SubfrmUniversalLoadListDetail.form!lngTrailerActivityHeaderId
<> 0 or
IsNull(Forms!frmLoadLists!SubfrmUniversalLoadListDetail.form!lngTrailerActivityHeaderId)
= False OR
Forms!frmLoadLists!SubfrmUniversalLoadListDetail.form!txtLoadListEntryId <>
'" & getWindowsUserId & "'", "")
With newCondition
.Enabled = True
.BackColor = -2147483633
.ForeColor = 0
End With
End If
End If
End With
Next i


'CAPTURE THE DIMENSIONS AND BOOTH NUMBER AS NEEDED
Set ctl = Me.subfrmUniversalLoadListDetail.Form.Controls("txtDimensions")
With ctl
Set newCondition = .FormatConditions.Add(acExpression, ,
"Forms!frmLoadLists!SubfrmUniversalLoadListDetail.Form!blnCaptureDimensions =
0 Or
IsNull(Forms!frmLoadLists!SubfrmUniversalLoadListDetail.Form!cboPartDescription)", "")
With newCondition
.Enabled = True
.BackColor = -2147483633
.ForeColor = 0
End With
End With
Set ctl = Me.subfrmUniversalLoadListDetail.Form.Controls("txtBoothNumber")
With ctl
Set newCondition = .FormatConditions.Add(acExpression, ,
"Forms!frmLoadLists!SubfrmUniversalLoadListDetail.Form!blnCaptureBoothNumber
= 0 Or
IsNull(Forms!frmLoadLists!SubfrmUniversalLoadListDetail.Form!cboPartDescription)", "")
With newCondition
.Enabled = True
.BackColor = -2147483633
.ForeColor = 0
End With
End With

Exit_setFormatConditionsSubfrmUniversalLoadListDetail:
Set newCondition = Nothing
Set ctl = Nothing
Exit Sub

Err_setFormatConditionsSubfrmUniversalLoadListDetail:
strMsgText = ""
strMsgText = strMsgText & getDefaultErrorMessage(Me.Name,
"setFormatConditionsSubfrmUniversalLoadListDetail", Err.Number,
AccessError(Err.Number)) & Chr(13) & Chr(13)
strMsgText = strMsgText & "This error may not allow this form to
function properly."
MsgBox strMsgText, vbCritical
MsgBox strMsgText
Resume Exit_setFormatConditionsSubfrmUniversalLoadListDetail

End Sub

 

[David H]

While that will work if the form is displaying a single record, if the OP is
using a continuous form, that will change the backcolor of the form on ALL of
the records as in.

vbBlue
vbBlue
vbBlue
vbBlue

VBA code. Use an IF statement in the after update event of the CODE control.

IF isnull(me.code) then
me.[PR #].backcolor = vbBlue
End If

Hi,

I have a form in which I would like to format the background color of a
field control based on the criteria in another field control which contains
10 different criteria. I would like the background color to be distinct
based on the criteria choosen.

For example, The field control is called "code" and my criteria for that
field are 0, 1, 1.5, 2, 3, 3.5, 3.6, 3.7, 4 and 5. The field control I would
like to format is called "PR #". If "0" is choosen in the "Code" field, I
would like the background color of the "PR #" to be blue, if "1" is choosen I
would like the background color to be gray etc.

How do I do this?