Conditionally formatting labels and other controls (2 Viewers)

[CJ_London]

Taking my inspiration from this thread concerning buttons on a continuous form

Button visible/invisible in a Continuous Form

I'd forgotten this - so simple & effective - have just used it for a web article https://www.isladogs.co.uk/paint-me-transparent/index.html Thanks again @Saphirah

www.access-programmers.co.uk

I investigated what else could be done with the paint function since conditional formatting a) does not allow you to change a border colour and b) only applies to textbox and combo controls

I first looked at which controls could be painted. On a single form below are all the standard controls that can be painted and show the range of options. Note that some controls, although they have a backcolor property, this cannot be changed in the paint event - further investigation required.

I next looked at continuous forms. Note that charts, subforms and web controls cannot be displayed in the detail section so have been excluded. The formatting is based on which record currently has the focus (2nd one in this case)

There appears to be two problems - The first is buttons, toggles and listboxes having been assigned formatting don't lose their formatting when focus is moved to another record. The other (which you'll need to open the attachment to see) is there is flicker when the mouse moves over a button or toggle control. I have not investigated either.

The test code for both of the above is

Private Sub Detail_Paint()
Dim ctl As Control


For Each ctl In Section(0).Controls
    'borders
    ctl.SpecialEffect = 0 'needs to be flat(0) or shadowed(4) for other border effects to work. Otherwise will paint as specified
    If Nz(Text15) = Text0 Then ctl.BorderColor = vbRed Else ctl.BorderColor = vbBlack
    If Nz(Text15) = Text0 Then ctl.borderWidth = 2 Else ctl.borderWidth = 1
    If Nz(Text15) = Text0 Then ctl.BorderStyle = 2 Else ctl.BorderStyle = 1
  
    'back
    On Error Resume Next
    If Nz(Text15) = Text0 Then ctl.BackColor = vbGreen Else ctl.BackColor = vbWhite
  
    'fonts - changing font, weight, underline, italic, color causes problems on continous form. Leave others to investigate
'    If Nz(Text15) = Text0 Then ctl.FontName = "Broadway" Else ctl.FontName = "Calibru"
'    If Nz(Text15) = Text0 Then ctl.FontSize = 12 Else ctl.FontSize = 9
'
'    If Nz(Text15) = Text0 Then ctl.FontWeight = 3 Else ctl.FontWeight = 1
'    If Nz(Text15) = Text0 Then ctl.FontUnderline = True Else ctl.FontUnderline = False
'    If Nz(Text15) = Text0 Then ctl.FontItalic = True Else ctl.FontItalic = False
'
'    If Nz(Text15) = Text0 Then ctl.ForeColor = vbRed Else ctl.ForeColor = vbBlack
'


Next ctl


End Sub


Private Sub Form_Current()


    Text15 = Text0
  
End Sub

Moving on to a practical application. This example uses a box control in the detail section to highlight the current record. The usual way to do this is to use an unbound textbox and conditional formatting. Probably due to the number of controls being low (1) there is no flicker.

and the code is

Private Sub Detail_Paint()
Static bColor As Long


    If bColor = 0 Then bColor = boxSelected.BorderColor 'set the color required in form design
    If Nz(txtSelected) = Me.CustomerID Then boxSelected.BorderColor = bColor Else boxSelected.BorderColor = vbWhite


  
End Sub


Private Sub Form_Current()


    txtSelected = CustomerID


End Sub

The alternative is to modify the detail section backcolor

and the code

Private Sub Detail_Paint()
  
    If Nz(txtSelected) = Me.CustomerID Then Detail.BackColor = 10213059 Else Detail.BackColor = vbWhite
    If Nz(txtSelected) = Me.CustomerID Then Detail.AlternateBackColor = 10213059 Else Detail.AlternateBackColor = vbWhite
  
End Sub


Private Sub Form_Current()


    txtSelected = CustomerID


End Sub

I did play around with changing font properties, but this created other issues - the code is in the attached if you want to see for yourself.

In summary - it is a method for extending some elements of conditional formatting to other controls that do not otherwise have conditional formatting

edit: file now attached