The Access database I work with contains specific information about records as they are processed. The records are organized by state.
Users can generate a report concerning all the records of a specific state. Each row of the report shows information about a specific record, in a pattern like this -
Row 1, Record 1: Field 1 Field 2 Field 3 Field 4 Field 5 Checkbox 1
Row 2, Record 2: Field 1 Field 2 Field 3 Field 4 Field 5 Checkbox 1
Row 3, Record 3: Field 1 Field 2 Field 3 Field 4 Field 5 Checkbox 1
In the data entry form, Checkbox 1 is checked when the processing of a record is finished. I added Checkbox 1 to the report as an object that is not visible on the report. Checkbox 1 occupies a field in the same table where Fields 1 through 5 reside.
If Checkbox 1 is checked for a specific record, I would like to cause the BackColor property to be yellow for all the fields in the row concerning that record; if Checkbox 1 is not checked, I would like the BackColor property to be white. This way, the user can easily distinguish between the records that have been processed from those that have not.
I have not had much success with this. For example, the code below highlights the entire column (i.e., all records or rows) with the field entitled "fldDATE", even if "fldUSED" (i.e., Checkbox 1) has been checked for only one record or row in the dataset.
In the code below, "fldUSED" is the equivalent of Checkbox 1 described above. "fldDATE" represents a test field that I added to see how the coding would play out in practice. "fldCOUNTER" is an Auto Number in the table where all the records and fields reside.
On its face, this does not seem to be all that difficult, but I am stymied. I have tried Case Select, For Each . . Next, multiple loops, other potions.
Private Sub Report_Load()
Static intCounter As Long
Dim intStop As Long
intCounter = DMin("[fldCOUNTER]", "tblRULESNOTICE")
intStop = DMax("[fldCOUNTER]", "tblRULESNOTICE")
Do While intCounter < intStop + 1
If Me.fldUSED = Yes Then
Me.fldDATE.BackColor = vbYellow
ElseIf Me.fldUSED = No Then
Me.fldDATE.BackColor = vbWhite
intCounter = intCounter + 1