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
