How can I conditionally format textbox border on continuous form?

Question

In Access 2010, I have a continuous form, and I'd like to change certain properties of a 'cell', like border colour, based on values in another cell. I haven't found a way to do this in VBA, because it's a continuous form and changing the properties directly in VBA changes it for all records, not just the one I'm on. I assume I have to use some form of conditional formatting, but the conditional formatting GUI only allows me to set background colour, basic text formatting, and the Enabled property.

How can I set other properties on a control on a continuous form, for specific records only?

Answer 1

You can't. Use background or text color.

See also https://msdn.microsoft.com/en-us/library/office/ff821010.aspx - there is only BackColor and ForeColor.

Answer 2

Feeding back how I got around this problem in the end. txtFixMax is the control I wanted to give a funky border to (dependent on a value in field [ChangedToday]). It already has conditional formatting to change the background colour. I created a second control, txtFixMaxOverlay, made it into a small square and placed it on top of the control txtFixMax. I set the properties of txtFixMaxOverlay to remove borders, and gave it the same conditional formatting as txtFixMax, so that it was invisible to the eye (but the Visible property = true). Then I gave it additional condition, the first one on the list, based on [ChangedToday], to change its background colour.

The effect isn't a border (although with a lot of tedious positioning of 4 controls I could have done this to give a border effect), but it does give me an extra element to visually change. The effect is:

Answer 3

Yes, you can... just not with the built-in conditional formatting functionality. Use the Paint event of the Form's Detail section. There are still various limitations, but at the least you can set more properties than just the background and foreground colors.

Example:

Private Sub Detail_Paint()
  If Me.IndicatorColumn.Value = "Critical" Then
    Detail.BackColor = RGB(255,0,0)
    Detail.AlternateBackColor = Detail.BackColor
    Me.AnotherColumn.BorderStyle = 7 'Dash Dot Dot
    Me.AnotherColumn.BorderColor = vbMagenta
  Else
    Detail.BackColor = vbWhite
    Detail.AlternateBackColor = RGB(150, 150, 150)
    Me.AnotherColumn.BorderStyle = 0 'Transparent
    Me.AnotherColumn.BorderColor = vbWhite
  End If
End Sub

See TextBox.BorderStyle.