As many of you know, you can set lots (Some say 32, some say 50) of Format Conditions in Design View by right clicking a text box and Selecting Conditional Formatting. But you try it in code and you will get errors if you try to set more than 3. (Internally, there is a Microsoft BUG, not a design limit, that resets the count when you touch conditions 0-3) I found the following will let me bypass that limit.
Does anybody know how to report a bug to Microsoft?
Do they even acknowledge that this is a bug?
Does anybody know how to report a bug to Microsoft?
Do they even acknowledge that this is a bug?
Code:
Public Sub SetConditionalFormating()
'---------------------------------------------------------------------------------------
' Procedure : SetConditionalFormating
' Author : Royce Fessenden
' Date : 11/22/2014
' Purpose : Sample workaround for the Access bug that limits the number of Format
' : Conditions that can be set in code to 3.
' :
' : ToUse: In design mode set up 3 or more FormatConditions. Conditions 0,1,2
' : (First thru third) can be conditions that will never change, or conditions
' : that will never be matched.
' :For example, if lngStatusId is always positive you can set the first conditions
' : to [lngStatusId] < 0.
' :
' :This has not been extensively tested, but it fixed my problem. I use Condition 0
' :for one that Is not changeable by the User. Conditions 1,2 are "never matches",
' : and the user can edit a table to set up conditions past the third one.
' :
' :
'---------------------------------------------------------------------------------------
'
Dim i As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
On Error GoTo Proc_Err
strSQL = "SELECT * From tblStatusCodes WHERE ysnFormatCondition = true"
Set rst = dbLocal.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rst.BOF Or Not rst.EOF Then
With Me.txtFirstField.FormatConditions
rst.MoveFirst
i = 3 ' Never touch 0,1,2 or it will limit the count to 3.
Do While Not rst.EOF
Debug.Print "Start Loop: "; i, Me.txtFirstField.FormatConditions.Count; " Formats"
If i < .Count Then
.Item(i).Modify acExpression, acEqual, "[lngStatusId]= " & CStr(rst!lngStatusId)
.Item(i).BackColor = Nz(rst!lngBackColor, vbWhite)
.Item(i).FontBold = Nz(rst!ysnBold, False)
.Item(i).ForeColor = Nz(rst!lngTextColor, vbBlack)
Else
Debug.Print "At Add: "; i, Me.txtFirstField.FormatConditions.Count; " Formats"
.Add acExpression, acEqual, "[lngStatusId]= " & CStr(rst!lngStatusId)
.Item(i).BackColor = Nz(rst!lngBackColor, vbWhite)
.Item(i).FontBold = Nz(rst!ysnBold, False)
.Item(i).ForeColor = Nz(rst!lngTextColor, vbBlack)
End If
rst.MoveNext
i = i + 1
Loop
End With
End If
Proc_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
Proc_Err:
MsgBox "Nasty Bug!"
'LogError Err.Number, Err.Description, mcModuleName, "SetConditionalFormating", vbNullString, gcGENERAL_ERROR, False
Resume Proc_Exit
End Sub
