Just incase it's something in the class module, here it is.
Option Compare Database
Option Explicit
'Name clsConditionalFormattingDataSheetView
'
'Purpose: Contains Custom property declarations
' to allow the user/developer
' to programmatically set the Conditional
Formatting
' properties of a TextBox.
' This first version contains only two Methods.
' #1)The background of the Current Row/Record is
highlighted
' #2) Alternate Rows of the Detail section are
highlighted
'
'Version: 1.1
'
'Calls: None
'
'Returns: Property values.
'
'INPUTS: The Control to be formatted.
'
'Created by: Stephen Lebans
'
'Credits: It's yours for the taking!<grin>
'
'Date: Feb 02, 2001
'
'Time: 11:11:11 pm
'
'Feedback: (e-mail address removed)
'
'My Web Page:
www.lebans.com
'
'Copyright: Lebans Holdings 1999 Ltd.
' Please feel free to use this code
' without restriction in any application you
develop,
' whether private or commercial.
' This code may not be resold by itself or as
' part of a collection.
'
'What's Missing!: Lots! This is just a start but you have to
start somewhere!
'
'
'
'Bugs: Let me know!
' Our Form
Private mForm As Access.Form
' The TextBox control that we use to cover the background of the Detail
Section
Private mKeyControl As Access.TextBox
' Color to use to highlight background of Detail Section
Private mHighlightColor As Long
' Boolean Flag to determine whether Highlighting is On/Off
Private mShowHighlighting As Boolean
' Boolean Flag to determine whether Alternate Row Highlighting is
On/Off
Private mShowHighlightingAlternate As Boolean
' Local var to hold copy of the Current Record number/position
' within the Form's Recordset.
Private mCurrentRecord As Long
Public Property Let KeyFieldControl(ctl As Access.TextBox)
If ctl Is Nothing Then
MsgBox "Error...you must supply a TextBox control containing the
Key field we can use to search this Form's recordset", vbCritical, _
"Init Key Field ERROR"
' Exit Property
End If
Set mKeyControl = ctl
'
'' Grab the Control's parent Form
'' Need to modify this to work with SubForms
Set mForm = ctl.Parent
'
'' Call our sub to setup the FormatConditions object
'SetupConditionalFormatting
'
'End Property
'
'
'Public Property Get BGTextBox() As Access.TextBox
'If mBGcontrol Is Nothing Then
' MsgBox "Error...you must supply a TextBox control for the Detail
Section!", vbCritical, _
' "Init Detail Section object ERROR"
' Exit Property
'End If
'Set BGTextBox = mBGcontrol
'
End Property
Public Property Let HighlightColor(color As Long)
mHighlightColor = color
' We could modify the FormatConditions object for each control
' if it exists instead of creating/recreating form scratch
' but we'll keep it simple for now.
' Call our sub to setup the FormatConditions object
SetupCF True
' Setup conditional format props
'Dim objFrc As FormatCondition
' Update the FormatConditions property for our TextBox.
' Verify it exists first.
'On Error Resume Next
'Set objFrc = mBGcontrol.FormatConditions(0)
'On Error GoTo 0
'If Not objFrc Is Nothing Then mBGcontrol.FormatConditions(0).BackColor
= mHighlightColor
End Property
Public Property Get HighlightColor() As Long
HighlightColor = mHighlightColor
End Property
Public Property Let ShowHighlighting(state As Boolean)
mShowHighlighting = state
If mShowHighlighting = True Then
' Call our setup Sub
'SetupConditionalFormatting
' Set the Alternate rows flag to false
mShowHighlightingAlternate = False
SetupCF True
Else
' Delete the FormatCondition object
'If Not mBGcontrol Is Nothing Then
' mBGcontrol.FormatConditions.Delete
'End If
SetupCF False
End If
End Property
Public Property Get ShowHighlighting() As Boolean
ShowHighlighting = mShowHighlighting
End Property
Public Property Let ShowHighlightingAlternateRows(state As Boolean)
mShowHighlightingAlternate = state
If mShowHighlightingAlternate = True Then
' Call our setup Sub for highlighting rows
SetupCF True 'SetupConditionalFormatting
Else
' Delete the FormatCondition object
'If Not mBGcontrol Is Nothing Then
' mBGcontrol.FormatConditions.Delete
SetupCF False
End If
End Property
Public Property Get ShowHighlightingAlternateRows() As Boolean
ShowHighlightingAlternateRows = mShowHighlightingAlternate
End Property
Public Sub Redraw()
If mCurrentRecord <> mForm.CurrentRecord Then
mCurrentRecord = mForm.CurrentRecord
mKeyControl.Requery
'mKeyControl.Parent.Refresh
End If
End Sub
Private Sub SetupCF(CreateOrDelete As Boolean)
' Loop through all of the controls in the Detail Section
' For any TextBox, Combo or ListBox controls that are visible
' setup the desired Conditional Formatting props
Dim ctl As Access.Control
For Each ctl In mForm.Section(acDetail).Controls
If TypeOf ctl Is Access.TextBox Or TypeOf ctl Is Access.ComboBox
Then ' Or TypeOf ctl Is Access.ListBox Then
If ctl.Visible = True Then
If CreateOrDelete = True Then
SetupConditionalFormatting ctl
Else
ctl.FormatCondition.Delete
End If
End If
End If
Next
End Sub
Private Sub SetupConditionalFormatting(ctl As Access.Control)
' Now setup the TextBox we are using to cover the
' background of the Detail section.
With ctl 'mBGcontrol
'.Visible = True
'.BackColor = mForm.Section(acDetail).BackColor
'.Enabled = False
'.TabStop = False
'.BackStyle = 1 'Normal
'.BorderStyle = 0 ' None
'.ControlTipText = ""
'.ControlSource = ""
'.DefaultValue = ""
'.ForeColor = vbWhite
'.Height = mForm.Section(.Section).Height
'.Left = 0
'.Locked = True
'.Top = 0
'.Width = mForm.Width
End With
' Setup conditional format props
Dim objFrc As FormatCondition
' Remove any existing format conditions.
ctl.FormatConditions.Delete
' Create a format object and add it to the FormatConditions collection.
' We have to pass to pass a value to the function otherwise
' Access will only execute the function once.
' See if we are Highlighting the Current Row or Alternate Rows
If mShowHighlightingAlternate = False Then
Set objFrc = ctl.FormatConditions.Add(acExpression, _
, "fCurrentRow([" & mKeyControl.Name & "])")
' , fCurrentRow([txtORDNO]))
Else
Set objFrc = ctl.FormatConditions.Add(acExpression, _
, "fAlternateRow([" & mKeyControl.Name & "])")
' , fAlternateRow([txtORDNO]))
End If
With ctl.FormatConditions(0)
.BackColor = mHighlightColor
'.Enabled = False
'.FontBold = True
'.ForeColor = vbBlack
End With
' Update our local var to reflect current position.
mCurrentRecord = mForm.CurrentRecord
' Cleanup
Set objFrc = Nothing
End Sub
Private Sub Class_Terminate()
Set mForm = Nothing
Set mKeyControl = Nothing
End Sub
