Updating conditional formatting in DataSheet Form

[luconsta]

Hello,

I need some help regarding conditional formatting into an DataSheet Form.
I have attached a small/basic sample database.
The setup is as follows:
- 3 tables: Orders and Order-Details (stripped so will remain only the required sample fields) and Order-Status
- One query - qryOrders - that will extract a list of orders with their details.
- One form - frmOrdersList - that will use as source the query and the status column is formatted using conditional formatting as expression:

[OrderStatus] Like "StatusName*"

so each status from the Order-Status table will be marked with a certain colour.

I need a way to set an certain status, and the conditional formatting to put the right colour when the Received =Ordered for the entire order.
Also, when someone will modify the Received so total received <> total ordered to put back an certain status.

The sample attached is using the Exit event of the Received field, and will change the formatting ONLY if after change the Received field value the user press DOWN arrow so will land on another record. Otherwise nothing happens and eventually will work if the user will press up/down arrow navigating through the Received column.

Is there a way to make this work also when the user press enter and so will select the next field on the right remaining on the same row?

 

[luconsta]

Well, the problem was solved. It seems that the record must be saved, this was done "by default" when leaving the record using DOWN ARROW.
So the exit event now look like this:

Private Sub Received_Exit(Cancel As Integer)
    Dim nOrdered As Double, nReceived As Double
    DoCmd.RunCommand acCmdSaveRecord
    nOrdered = DSum("Ordered", "Order-Detail", "OrderNo=" & Me.OrderNo.Value)
    nReceived = DSum("Received", "Order-Detail", "OrderNo=" & Me.OrderNo.Value)
   
    If (nOrdered = nReceived) And (InStr(1, Me.OrderStatus.Value, "ONE") <> 0) Then
        Me.OrderStatus.Value = "TWO"
        Me.Refresh
    ElseIf (nOrdered <> nReceived) And (InStr(1, Me.OrderStatus.Value, "TWO") <> 0) Then
        Me.OrderStatus.Value = "ONE"
        Me.Refresh
    End If
    
End Sub

So, case closed.