title: Access Conditional Formatting - Fewer VBA Options over user interface?
body: <p>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 in the code, then the code worked fine.  </p>
  
  <p>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.  But I know I can at least get more than 3 conditions.</p>
  
  <p>Here is the test code that works for up to 3 records:</p>
  
  <pre><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] = '" &amp; rs!TypeNm &amp; "'")
      '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
  </code></pre>
  
  <p>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."</p>
  
  <p>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?  </p>
  
  <p>Thank you!!</p>