Access 2010 VBA syntax for conditional formating

Topic

New Reply

I have a form frmRMA_Summary with a sub form sbfRMAs. My subform sbfRMAs is set to Datasheet view. I have been successful using the Conditional Formatting Rules Manager with this statement and desired formatting.

Code:

([bolRepair]=True And (Abs([me.txtReceivedDate]-Date())>14) And IsNull([me.txtShipDate]))

When I delete the rule and attempt this in VB of the sbfRMAs Form_Current the backcolor and forecolor settings do not take effect. However, the applicable Msgbox appears for each time a record meets the criteria.

Code:

    If ([bolRepair] = True And (Abs([txtReceivedDate] – Date) > 14) And IsNull([txtShipDate])) Then
‘MsgBox “Overdue, so color should change”
        Me.txtShipDate.BackColor = vbRed
        Me.txtShipDate.ForeColor = vbWhite
‘MsgBox [Forms]![frmRMA_Summary]![sbfRMAs]![txtShipDate].ForeColor
    Else
‘MsgBox “Not overdue, so no color change”
        Me.txtShipDate.BackColor = vbWhite
        Me.txtShipDate.ForeColor = vbBlack
    End If

The purpose of doing this in code rather than the Rules Manager is that we’d rather all settings, values, etc be controlled from VB. Possibility also exists that we implement more complex code at a later date.

Thank you,

Ken

Reply | Quote

Replies

If you use VBA to change the properties of a control in a continuous form, the change will apply to all records not just the current one. There is only set of properties.

Conditional formatting is the only way around that, which provides a very good reason to use conditional formatting rather than VBA.

I can’t see anything wrong with the code..but I wondered if the argument above may make the code irrelevant.

Reply | Quote

You can use VBA to manage conditional formatting (as distinct from using code and conditions to control formatting.)

Here is a lengthy post with some code showing how to do it.

Reply | Quote

Awesome John, thank you!!

Reply | Quote