HI there, I have an ms access report. I want to write vba code to give a different color to only the first three records

Question

I have an ms access report displaying multiple records. I want to display the first three records in a different background color, I want to write vba-code to accomplish this. Does anyone know how to accomplish this? Any help will be greatly appreciated, thanks.

I tried coding in detail-events, but then the whole section was displayed in another color.

Answer 1

This works in print preview:

SUB DETAIL_FORMAT
    IF (condition) THEN
        DETAIL.BACKCOLOR = ...
    ELSE
        DETAIL.BACKCOLOR = ...
    END IF
END SUB

This works in report view:

SUB DETAIL_PAINT
    IF (condition) THEN
        DETAIL.BACKCOLOR = ...
    ELSE
        DETAIL.BACKCOLOR = ...
    END IF
END SUB

Answer 2

Try this:

Place a textbox named tbxCnt in Detail section with ControlSource of =1 and RunningSum set to OverAll (can be set not visible). Code options:

1. Conditional Formatting rule on each textbox Expression is: [tbxCnt]<4

2. VBA can test for value of tbxCnt to set Detail section backcolor with:
   If Me.tbxCnt < 4 Then Me.Detail.Backcolor = vbYellow Else: Me.Detail.Backcolor = vbWhite
   or
   Me.Detail.Backcolor = IIf(Me.tbxCnt < 4, vbYellow, vbWhite)

Make sure AlternateBackColor is set for NoColor.