Interesting. I thought you had to convert the color number to hex but I don't see that you did so. I passed the form, field to sort by and the sort order to a public sub so that it could be used by any form. The gauntlet has been thrown down!
The sort is based on a form module level variable:
Option Compare Database
Option Explicit
Dim strSort As String
In control event (AfterUpdate or whatever)
Code:
Dim ctl As Control
On Error GoTo errHandler
If Not IsNullEmpty(Me.txtSearch) Then
For Each ctl In Me.Detail.Controls
If ctl.ControlType = acCommandButton And ctl.Tag <> "" Then
ctl.BackColor = GetRGB(ctl.Tag)
If ctl.Caption Like "*" & Me.txtSearch & "*" Then ctl.BackColor = vbYellow
End If
Next ctl
End If
exitHere:
On Error Resume Next 'possible to raise error before any object gets assigned to ctl, which in itself would raise an error
Set ctl = Nothing
Exit Sub
errHandler:
MsgBox "Error No. " & Err.Number & ": " & Err.Description
Resume exitHere
standard module:
Code:
Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = Nz(ctl, "") = ""
End Function
Function GetRGB(strVal As String) As Long
'code adapted from https://access-programmers.co.uk/for...d.php?t=267808
Dim strR As String, strG As String, strB As String
If Left(strVal, 1) = "#" Then strVal = Right(strVal, Len(strVal) - 1)
strR = Left(strVal, 2)
strG = Mid(strVal, 3, 2)
strB = Right(strVal, 2)
GetRGB = CLng("&H" & strR & strG & strB)
End Function
I took the liberty of basing the search on control type rather than cmd being part of a name string because I use cmd for all command buttons anyway, plus wanted to show OP the use of the control type property. Also, the IsNullEmpty function may prove useful...
I never bothered to deal with the Clear button, so I think you win.