Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2017
    Posts
    2
    Provided Answers: 1

    Answered: Conditional Formatting - Using Code Produces Error For 4+ Conditions - Why?

    The problem is that while the front-end allows 4 or more conditions, when I tried to set conditions using VBA, I ran into an error when setting the 4th condition. In other words, if I only tried to set 3 conditions, then the code worked fine.

    I am using MS Access 2010. I need to set conditional formatting for two textboxes on a continuous form. I know that older versions of MS Access allowed only 3 conditions on a textbox, but I know that I can get more conditions in Access 2010. (My current application has 4 conditions using the user interface.) In my research on this question, one person said that later versions of MS Access allow up to 50 conditions. I could not confirm this either way, even when I reviewed the Access 2010 specifications page.

    Here is the test code that works for up to 3 records:

    Code:
        Function fApplyConditionFormatNow()
    
        Dim objFormatConds As FormatCondition
        Dim i As Integer 'index number for specific format conditions
        Dim stSQL As String 'query to get list of categories
        Dim rs As DAO.Recordset
        
        i = 0
        
        'clear out just in case FormatConditions accidentially got saved
        'with the form at some point.
        Me.ID.FormatConditions.Delete
        
        'get a recordset containing the formatting information
        '(ie, get RGB values for each category type)
        stSQL = "SELECT * FROM tblTestConditionalFormatting;"
        fRunSQL stSQL, rs 'fRunSQL is custom code that gets runs stSQL and returns the recordset
        
        'loop through recordset to get conditional formatting values
        Do Until rs.EOF
            'create a condition on textbox named "ID".  The condition will be for
            'the Category/Type (TypeNm) that's up now in the recordset.
            Set objFormatConds = Me.ID.FormatConditions.Add(acExpression, , "[TypeNm] = '" & rs!TypeNm & "'")
            'add formatting for the condition just created.
            With Me.ID.FormatConditions(i)
                .BackColor = RGB(rs!RGB1, rs!RGB2, rs!RGB3)
            End With
            i = i + 1
            rs.MoveNext
        Loop
        
        rs.Close
        Set rs = Nothing
        
        End Function
    When 4 records are included in the category table : ie, tblTestConditionalFormatting, I get the following error:
    "Runtime error 7966: The format condition you specified is greater than the number of format conditions."

    So, there appears to be a bug in that the front-end can handle more than 3 conditions but the VBA object can only handle 3 conditions? OR maybe I'm doing something wrong. Has anyone else come across this? Do you have an idea for a work around?

    Thank you!!

  2. Best Answer
    Posted by AmandaHere

    "Thanks for the reply ranman! I didn't think the on_current event idea would work for a continuous form. I gave it a try after seeing your post just in case, but I could not get it to work.

    Anyway, a poster on StackOverflow found the answer! This problem is indeed a bug in Access, but there is a work around. The following site explains the work-around: https://access-programmers.co.uk/for...d.php?t=271679

    Basically, you have to set 3 dummy conditions in the user interface for conditional formats. Then you can set real conditions in the code as long as you do not touch the first 3 conditions.

    Thanks again for your reply. I appreciate it."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    You can skip the conditional format, and put the formatting in the Form On current event.
    Set the text box color using vb....

    Code:
    Sub Form_OnCurrent()
    Select case true
         Case condition1
               TxtBox.backcolor = vbRed
       Case condition2
               TxtBox.backcolor = vbGreen
     End select
    End sub

  4. #3
    Join Date
    Jan 2017
    Posts
    2
    Provided Answers: 1
    Thanks for the reply ranman! I didn't think the on_current event idea would work for a continuous form. I gave it a try after seeing your post just in case, but I could not get it to work.

    Anyway, a poster on StackOverflow found the answer! This problem is indeed a bug in Access, but there is a work around. The following site explains the work-around: https://access-programmers.co.uk/for...d.php?t=271679

    Basically, you have to set 3 dummy conditions in the user interface for conditional formats. Then you can set real conditions in the code as long as you do not touch the first 3 conditions.

    Thanks again for your reply. I appreciate it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •