Format fields in subform records using VBA

Question

I'm trying to use VBA to format some unbound textboxes I've added to the rows on a subform set to continuous view.

The VBA looks at the bound text boxes associated with the subform's underlying RecordSource and then formats the unbound textboxes I've added based on the data.

Here's some simplified code:

Public Sub ApplyFormat()

    If Forms!tblEnrolments!tblEnrolments_Jobs_sub!Start = 1 Then

        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart.BackColor = RGB(65, 138, 179)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart.ForeColor = RGB(255, 255, 255)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart = "Start Forms"

    ElseIf Forms!tblEnrolments!tblEnrolments_Jobs_sub!Start = 0 Then

        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart.BackColor = RGB(216, 216, 216)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart.ForeColor = RGB(166, 166, 166)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtStart = "None"

    End If

    If Forms!tblEnrolments!tblEnrolments_Jobs_sub!End = 1 Then

        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd.BackColor = RGB(8, 164, 71)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd.ForeColor = RGB(255, 255, 255)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd = "End Forms"

    ElseIf Forms!tblEnrolments!tblEnrolments_Jobs_sub!End = 0 Then

        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd.BackColor = RGB(216, 216, 216)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd.ForeColor = RGB(166, 166, 166)
        Forms!tblEnrolments!tblEnrolments_Jobs_sub!txtEnd = "None"

    End If

End Sub

Private Sub Form_Activate()

    ApplyFormat

End Sub

Private Sub Form_Current()

    ApplyFormat

End Sub

So I have a public sub called ApplyFormat that looks at the data in the bound textboxes and then applies formatting (changes the textbox background colour, font colour and text data). This is then called from the Current event (so formats are applied whenever a record is loaded) and the Activate event (because the main form will open other forms whilst still being open and may get focus again when those other forms close).

Unfortunately, records below record 1 in the subform are not being formatted based on data in their record; they are just simply duplicating the formatting applied at the first record.

For example:

Here EnrolID "1" has 2 attached jobs showing in the subform. The first record is formatting correct as per the code above, i.e. the bound "Start" text box is 1 so the unbound text box next to it is being formatted as blue with white text and the string "Start Forms".

In the second record however, the "Start" text box is 0, which should format the unbound text box next to it as grey with the string "None". But as you can see it's just copying the formatting of the first record. Furthermore, the bound text box "End" has a value of 1, which should make the unbound text box next to it green with white text and the string "End Forms".

Is it possible it possible to achieve the effect I'm going for via VBA? I don't think I can use conditional formatting as I want the unbound text boxes to show data such as "Start Forms" and "End Forms" depending on their bound text box counterpart.

Answer 1

You cannot use unbound fields for that: Unbounds fields in continuous forms always have the same formatting in all rows. It's an unfortunate limitation.

Fortunately, there is another option: You can make a bound field, with a ControlSource that calls a user-defined function. For example, in the Form desginer, create a text box and set its "control source" property to = Iif([Start] = 1, "Start Forms", "None") (including the = sign at the start). The formatting would have to be done through conditional formatting.

If you need more complicated logic to determine the text to be shown, you can set the control source to = myCustomFunction([any], [dbfields], [I], [need]) and have the VBA code return the text to be shown.