How to seperate recordsets in form display

[pleasehelpalot]

A form displays a set of filtered records from a main table. Fields two and three are (2)yr/qtr and (3)audit type. When the combination changes the user would like to seperate the records by either color or a space. The reason is because as he scrolls right he loses sight of which recordset he started checking. It would be a lot clearer if the records were seperated in some fashion.

Is there a way to do this? Where? How?

 

[MajP]

You can do this with conditional formatting. But I will explain it with one field.

Lets say your data looks like

Item Order ID
1 abc
2 abc
3 abc
4 cde
6 cde
8 efg
9 efg

and you want to alternate colors for each different order ID

do a select distinct qry on OrderID the results look like

qryDistinct: OrderID
abc
cde
efg

now but a textbox behind all of your other controls and put conditional formatting on it.
build a custom function

 Public Function AltBkg(frmRpt As Object, PKname As String) As Boolean
   'place the following in the form's module:
   'Public Function AltPrep() As Boolean
   '   AltPrep = AltBkg(Me, "OrderID")
   'End Function
   '
   'in conditional formatting: "EXPRESSION IS AltPrep()=True"
   '
   Dim rsDistinct As DAO.Recordset
   Set rsDistinct = CurrentDb.OpenRecordset("qryDistinct", dbOpenDynaset)
   rsDistinct.FindFirst "[" & PKname & "] =" & frmRpt(PKname)
   If (rsDistinct.AbsolutePosition + 1) Mod 2 = 0 Then
      AltBkg = True
   End If
End Function

This code is based on AceMan's original code for alternating rows