ms-access: how to fit VERY long condition in conditional formatting

Question

i have a very long condition. it's about 3000 characters

access has only space for about one tenth of that. is there some other way to set conditional formatting on a textbox besides through the dialogue? can i do it in VBA? if so HOW?

i have conditional formatting on a bunch of textboxes that trigger when the report is opened

Answer 1

According to Access' Help topic, the FormatConditions Collection has methods (Add, Delete, Modify) which should allow you to adjust your FormatConditions with VBA code. I've never tried, so offer no opinion as to whether this would be a practical approach for you.

I also tried to find out if there is a capacity limit for the number of characters FormatConditions can accept. I didn't find anything there.

Answer 2

Yes, you can manipulate Format Conditions in VBA. There's a full detailed article knowledgebase article at http://support.microsoft.com/kb/304104.

Code snippet here showing a basic example. Refer to the above link to get the VBA for AddFormats:

Public Function HighLightForeignKeys(argFieldName As String, argFieldValue As Integer)
    Dim FormatCondition As String
    Dim CodeReception As Integer
    FormatCondition = "[" & argFieldName & "] = " & ArgFieldValue
    With Me.ID
        .FormatConditions.Delete
        .FormatConditions.Add acExpression, , FormatCondition
        .FormatConditions(0).BackColor = 16510422
        AddFormats Me.ID, Me
    End With

End Function