2424 or 2450 error

[MajP]

But with conditional formatting I would be limited to 50 colors, right?

You may be able to do a work around. Would one order ever have more than 50 colors? If not then you could on the current event remove the format conditions and re-add only those colors for that Order. It would be near instantaneous so time is not an issue.

 

[isladogs]

Although conditional formatting has a limit of 50 colours, a slightly different approach will allow an unlimited number of colours.

Instead of using conditional formatting, all the colours are stored in a lookup table
See my article and example app:

Colour Conditions

50 colour formats are available using conditional formatting but only 3 colours are available using VBA.This shows another method of obtaining an unlimited number of colour formats in VBA without using conditional formatting.

www.isladogs.co.uk

 

[reggied]

Yep. This works no flicker. need to remove the errors in the color query.

Private Sub Form_Load()
   removeFormatConditions
   AddFormatConditions
End Sub

Public Sub AddFormatConditions()
  Dim rs As DAO.Recordset
  Dim color As Long
  Dim Finish As Long
  Dim con As FormatCondition
  Set rs = CurrentDb.OpenRecordset("qryColors")
  Do While Not rs.EOF
    color = rs!hextorgb
    Finish = CLng(rs!FinishID)
    Set con = Me.cboFinishID.FormatConditions.Add(acFieldValue, acEqual, Finish)
    With con
      'forcolor if you wanted
      .BackColor = color
    End With
    rs.MoveNext
  Loop
End Sub
Public Sub RemoveFormatConditions()
 Dim con As FormatCondition
 For Each con In Me.cboFinishID.FormatConditions
    con.Delete
 Next con
End Sub

This works great! I just had to make a small adjustment to the HextoRGB field to give a 0 for few of the color codes.

Just one clarification: this will only work for up to 50 colors?

 

[reggied]

You may be able to do a work around. Would one order ever have more than 50 colors? If not then you could on the current event remove the format conditions and re-add only those colors for that Order. It would be near instantaneous so time is not an issue.

Thanks for this advice! Each order typically has only 3-4 different colors at at time. You are suggesting delete the code in the on load event and move it to the current event of the subform? Remove first, then add in the current event?

 

[MajP]

Although conditional formatting has a limit of 50 colours, a slightly different approach will allow an unlimited number of colours.

Instead of using conditional formatting, all the colours are stored in a lookup table
See my article and example app:

Colour Conditions

50 colour formats are available using conditional formatting but only 3 colours are available using VBA.This shows another method of obtaining an unlimited number of colour formats in VBA without using conditional formatting.

www.isladogs.co.uk

But that is the problem the OP is trying to avoid with the Paint event. The OP already has the colors in the table. My question is why some forms like yours have no issue with the paint event, but he has the bad flicker.

 

[MajP]

This should give you 50 colors per order. On current remove all conditions, loop only the colors in that order and add those conditions.

Private Sub Form_Current()
    If Not IsNull(DLookup("ID", "MSysObjects", _
    "(Name = 'Paste Errors') AND (Type = 1)")) Then
        Me.cmdViewPasteErrors.Visible = True
        Me.cmdDeletePasteErrors.Visible = True
    Else
        Me.cmdViewPasteErrors.Visible = False
        Me.cmdDeletePasteErrors.Visible = False
    End If
   
    RemoveFormatConditions
    AddFormatConditions
End Sub

Public Sub RemoveFormatConditions()
 Dim con As FormatCondition
 For Each con In Me.cboFinishID.FormatConditions
    con.Delete
 Next con
End Sub

Public Sub AddFormatConditions()
  Dim rs As DAO.Recordset
  Dim color As Long
  Dim Finish As Long
  Dim con As FormatCondition
  Dim strSql As String
 
  Set rs = CurrentDb.OpenRecordset("qryColors")
  strSql = "SELECT LI.SalesOrderID, LI.FinishID, C.HextoRGB FROM tblSOLineItems AS LI INNER JOIN qryColors AS C ON LI.FinishID = C.FinishID "
  strSql = strSql & "WHERE Li.SalesOrderID = " & Me.SalesOrderID
  strSql = strSql & " GROUP BY LI.SalesOrderID, LI.FinishID, C.HextoRGB"
 
  Do While Not rs.EOF
    color = rs!hextorgb
    Finish = CLng(rs!FinishID)
    Set con = Me.cboFinishID.FormatConditions.Add(acFieldValue, acEqual, Finish)
    With con
      'forcolor if you wanted
      .BackColor = color
    End With
    rs.MoveNext
  Loop
End Sub

 

[isladogs]

But that is the problem the OP is trying to avoid with the Paint event. The OP already has the colors in the table. My question is why some forms like yours have no issue with the paint event, but he has the bad flicker.

Clearly I only skim read the thread! No idea .... but I get no flicker with the use of the Detail_Paint event in my code / example app.
I've been using that approach for 20+ years originally as a workaround for the then limit of 3 CF conditions

 

[reggied]

This should give you 50 colors per order. On current remove all conditions, loop only the colors in that order and add those conditions.

Private Sub Form_Current()
    If Not IsNull(DLookup("ID", "MSysObjects", _
    "(Name = 'Paste Errors') AND (Type = 1)")) Then
        Me.cmdViewPasteErrors.Visible = True
        Me.cmdDeletePasteErrors.Visible = True
    Else
        Me.cmdViewPasteErrors.Visible = False
        Me.cmdDeletePasteErrors.Visible = False
    End If
  
    RemoveFormatConditions
    AddFormatConditions
End Sub

Public Sub RemoveFormatConditions()
 Dim con As FormatCondition
 For Each con In Me.cboFinishID.FormatConditions
    con.Delete
 Next con
End Sub

Public Sub AddFormatConditions()
  Dim rs As DAO.Recordset
  Dim color As Long
  Dim Finish As Long
  Dim con As FormatCondition
  Dim strSql As String
 
  Set rs = CurrentDb.OpenRecordset("qryColors")
  strSql = "SELECT LI.SalesOrderID, LI.FinishID, C.HextoRGB FROM tblSOLineItems AS LI INNER JOIN qryColors AS C ON LI.FinishID = C.FinishID "
  strSql = strSql & "WHERE Li.SalesOrderID = " & Me.SalesOrderID
  strSql = strSql & " GROUP BY LI.SalesOrderID, LI.FinishID, C.HextoRGB"
 
  Do While Not rs.EOF
    color = rs!hextorgb
    Finish = CLng(rs!FinishID)
    Set con = Me.cboFinishID.FormatConditions.Add(acFieldValue, acEqual, Finish)
    With con
      'forcolor if you wanted
      .BackColor = color
    End With
    rs.MoveNext
  Loop
End Sub

Gasp! You are an Access VBA Savant!
Works like a charm. I only added an If Then statement to differentiate backcolor and forecolor for non-colors (white or raw).

    With con
      If color = 0 Then
      .ForeColor = RGB(0, 0, 0)
      .BackColor = RGB(255, 255, 255)
      Else
      .ForeColor = RGB(255, 255, 255)
      .BackColor = color
      End If
    End With

Thank you!

 

[reggied]

Clearly I only skim read the thread! No idea .... but I get no flicker with the use of the Detail_Paint event in my code / example app.
I've been using that approach for 20+ years originally as a workaround for the then limit of 3 CF conditions

The flicker was not the big problem (I haven't tested this out on other machines but it could be my machine is a little slower).

I couldn't figure out why I was getting the 2424 error from the subform when switching records on the parent form. But per MajP it may be a timing issue related to when the Detail_Paint event fires.

 

[MajP]

I was getting flicker too.