Color entire row in continuous form depending on field value(s)

[jhg6308]

The objective is to have a display-only continuous form where each row's background color depends on the value of one or more fields in that row.



Continuous forms don't seem to provide good event handling. I naively expected that OnCurrent would work for this, but it fires only for the first record. After thinking about this it makes sense as there's only one "Current" record at a time for a form.

I then tried making the fields all transparent and hooking the Detail_Paint event, and got almost what I want:

Private Sub Detail_Paint()

    If IsNull(orderDate) Then ' This is a usage row, color it light pink
        Me.Section(acDetail).BackColor = RGB(255, 240, 255)
        Me.Section(acDetail).AlternateBackColor = RGB(255, 240, 255)
    
    ElseIf Paid Then ' This is a paid purchase row, color it light green
        Me.Section(acDetail).BackColor = RGB(240, 255, 250)
        Me.Section(acDetail).AlternateBackColor = RGB(240, 255, 250)
    
    Else ' This is an unpaid purchase row, make it light gray
        Me.Section(acDetail).BackColor = RGB(240, 240, 240)
        Me.Section(acDetail).AlternateBackColor = RGB(240, 240, 240)
    
    End If

End Sub

Gives me this:



It's exactly what I want, but it leaves the unused portion of the continuous form painted in the last color encountered.

Ideally I'd want an event that fires after a continuous form has been populated, so I can reset the section background colors, but that does not seem to exist.

Conditional formatting works only on individual fields and cannot be applied to a section.

Is there a way to accomplish my goal by either:

1) resetting the section background after the continuous form is populated, or
2) a completely different approach

ADDENDUM: It looks like I might be able to do what I want with Conditional Formatting if I don't leave empty space between fields.
However, this means setting up conditional formatting for every field in the UI.
Is there a way to manipulate conditional formatting from VBA? I can't find any methods or properties for this on the Form, Control or individual control objects.

[Micron]

but it fires only for the first record.

Don't agree. It fires each time you move to a different record, either via navigation controls or scrolling and choosing another record. Regardless, I doubt it matters. CF often works - as long as you place one row-long textbox behind all the others in the row, format the top controls according to how you want this to look, and set CF on the 'row' textbox using an expression. Perhaps set to yellow if txt1 = 1 AND txt2 = 2 AND txt3 = 3 for example and add up to 2 more conditions.

Alternatively, you can keep the background textbox and create a SELECT CASE block to expand the conditions since CF only allows for 3 IIRC, again, using the current event.

[jhg6308]

Don't agree. It fires each time you move to a different record, ...

True, but while it's populating the continuous form, only the first record is "current". OnCurrent for a row will fire only when the user moves to a different record in the continuous form.

[ssanfu]

..... CF often works - as long as you place one row-long textbox behind all the others in the row, format the top controls according to how you want this to look, and set CF on the 'row' textbox using an expression.

I've used this idea for many, many years.
Here is my example of what Micron described.... (using only CF)

[jhg6308]

... CF often works - as long as you place one row-long textbox behind all the others in the row, format the top controls according to how you want this to look, and set CF on the 'row' textbox using an expression.

Perfect... I'm still learning the intricacies of Access forms.

Thanks

[Micron]

only the first record is "current". OnCurrent for a row

Sorry, no. The current event is for the form. It has nothing to do what record you are on. One thing the current event can do is tell you a value for any field in a record that has been selected but there is no such thing as a current event for a record or as you put it, a "row".

will fire only when the user moves to a different record in the continuous form.

Also wrong. When a form opens, it fires as either the 4th or 5th event, depending on whether or not the form has any controls at all. A form with no controls still fires the event. No control means no records, so your statement is incorrect. It also fires whenever the active record becomes some other record and whether or not it is the first record has no bearing.

One thing I forgot to clarify regarding row background colour depending on field values and using code is that the the method still involves CF. IIRC, the limit is still three formatting conditions if using CF in vba. However, SELECT would provide more logical conditions. Attempting to set the background color property of an unbound textbox would affect all copies of the textbox, so just using that property is of no use.