Flashing Rows in Access Form Datasheet

Question

I want some records in my Access Datasheet Form to flash (switch from one color to another one) depending on the value in a given field.

I found some great help on there by using the On Timer event of the Form but for some reason it doesn't work for me. I suppose it is because in my case, my form is displayed in Datasheet View.

Also, I realized that it only checks the value of the active record but I would like it to check the value of all the records for that given field (via SQL maybe ?)

Is it even possible to change the back color of a record via VBA in Access or the only way is via the conditional formatting tool ?

I am a bit confused on how to proceed and if there is even a way to do so.

Any ideas on how to do this ?

Private Sub Form_Timer()

   If [Receiver] = "Martin" Then
       If [Receiver].ForeColor = 0 Then 'vbRed
          [Receiver].ForeColor = 255 'vbBlack
       Else
          [Receiver].ForeColor = 0 'vbRed
       End If
   End If

End Sub

Answer 1

Add the following to your form's code file assuming the TextBox control you need to flash is named txtReceiver:

Private Sub Form_Load()
    Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
    With Me.txtReceiver
        If .BackColor = vbRed Then
            .BackColor = vbBlack
            .ForeColor = vbWhite
        Else
            .BackColor = vbRed
            .ForeColor = vbBlack
        End If
    End With
End Sub

The above will set the BackColor/ForeColor to all TextBox controls named txtReceiver so you need to set the conditional formatting to adjust this:

Rule:          Field Value Is not equal to Martin
BackColor: White
ForeColor: Black

All TextBox controls named txtReceiver with a value of "Martin" will now flash.

Answer 2

I found a way to make it "flash" but the colour updates approx. avery 10 seconds and it obviously slows (sometimes crashes) the database.

Even though some might think making it flash is not the best way, this is still want I am trying to achieve, so if you have any ideas how I could make it work properly let me know.

This is what I have done :

1. I have created an extra field Random which stores either "Yes" either "No"
2. In the conditional formatting of the form, I wrote this :

3. In the On Timer event I linked it to the following code where the last part only runs in loop :

   Function AWBInput()
   Dim i As Integer
   Dim NewRandom As String
   
   Dim conn As New ADODB.Connection
   Dim connStr As String
   Dim rs As ADODB.Recordset
   
   connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "P:\MyPath\" & "PartsTLC.accdb" & ";"
   conn.ConnectionString = connStr
   conn.Open
   
   ' Store Random
       Dim RandomSQL As String
       Dim arrayRandom() As Variant
       Set rs = New ADODB.Recordset
       RandomSQL = "SELECT [Random] FROM List"
       rs.Open RandomSQL, conn, adOpenStatic, adLockReadOnly, adCmdText
       If Not rs.EOF Then
           arrayRandom = rs.GetRows
       End If
       Set rs = Nothing
   
   ' Store ID
       Dim IDSQL As String
       Dim arrayID() As Variant
       Set rs = New ADODB.Recordset
       IDSQL = "SELECT [ID] FROM List"
       rs.Open IDSQL, conn, adOpenStatic, adLockReadOnly, adCmdText
       If Not rs.EOF Then
           arrayID = rs.GetRows
       End If
       Set rs = Nothing
   
   
   Dim NumberOfFlashes As Integer
   Dim WaitUntil
   ANouveau:
   
   'WaitUntil = Now + TimeValue("00:00:5")
   '    Do
   '        DoEvents
   '    Loop Until Now >= WaitUntil
   NumberOfFlashes = NumberOfFlashes + 1
   Screen.ActiveForm.Refresh
   Do
   For i = 0 To UBound(arrayRandom, 2)
       If arrayRandom(0, i) = "Yes" Then
                       NewRandom = "No"
                       arrayRandom(0, i) = NewRandom
                       DoCmd.RunSQL "UPDATE List Set [Random] = '" & NewRandom & "' WHERE [ID] = " & arrayID(0, i)
       Else:           NewRandom = "Yes"
                       arrayRandom(0, i) = NewRandom
                       DoCmd.RunSQL "UPDATE List Set [Random] = '" & NewRandom & "' WHERE [ID] = " & arrayID(0, i)
       End If
   Next i
   GoTo ANouveau
   Loop While NumberOfFlashes < 10000
   End Function