Apply Conditional Formatting to Consecutive Records w/ Same Value

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I use a query expression that calculates the following:

Total: CDbl(Nz([X] * 0.6 + [Y] * 0.4, 0))


This query is then linked to a form. The records in the form are shown in
"Datasheet view" and are sorted by the "Total" field.

I want to apply conditional formatting for the "Total" field and show where
consecutive values are equal.

For instance:

Name Field X Field Y Total
Tom 50 5 32.0
Helen 20 10 16.0
John 24 4 16.0
Kathy 12 9 10.8
Don 14 3 9.6

Again, in the form, I now want to highlight "16" for the record of "Helen"
and "John".

Does anyone know how to approach this?


Thank you,
Tom
 
So you need some way of retrieving the value from the previous record in the
form, so you can determine whether the current value is the same as the
previous row.

There are several traps in gettting that to work, e.g.:
a) It has to work correctly, even if the form is filtered or sorted
differently.
b) You are working with a calculated field here.
c) The field is a Double, so the comparision may be defeated by rounding
errors.
d) Conditional formatting is buggy, and it is *very* easy to trigger an
endless loop (esp. with calcuated fields), so that the status bar says
"Calculating" endlessly and the results do not show as expected.

If you want to try it anyway, paste the function below into a general
module, and save. Then in the Conditional Formatting for your field, try an
Expression something like:
Abs([Total] - GetPreviousValue([Form], "Total")) < 0.0001
where Total represents the name of your calculated field.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
'Keywords: PriorRow PreviousValue
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function
 
Thanks, Allen,

that works!

Tom



Allen Browne said:
So you need some way of retrieving the value from the previous record in the
form, so you can determine whether the current value is the same as the
previous row.

There are several traps in gettting that to work, e.g.:
a) It has to work correctly, even if the form is filtered or sorted
differently.
b) You are working with a calculated field here.
c) The field is a Double, so the comparision may be defeated by rounding
errors.
d) Conditional formatting is buggy, and it is *very* easy to trigger an
endless loop (esp. with calcuated fields), so that the status bar says
"Calculating" endlessly and the results do not show as expected.

If you want to try it anyway, paste the function below into a general
module, and save. Then in the Conditional Formatting for your field, try an
Expression something like:
Abs([Total] - GetPreviousValue([Form], "Total")) < 0.0001
where Total represents the name of your calculated field.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
'Keywords: PriorRow PreviousValue
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
I use a query expression that calculates the following:

Total: CDbl(Nz([X] * 0.6 + [Y] * 0.4, 0))


This query is then linked to a form. The records in the form are shown
in
"Datasheet view" and are sorted by the "Total" field.

I want to apply conditional formatting for the "Total" field and show
where
consecutive values are equal.

For instance:

Name Field X Field Y Total
Tom 50 5 32.0
Helen 20 10 16.0
John 24 4 16.0
Kathy 12 9 10.8
Don 14 3 9.6

Again, in the form, I now want to highlight "16" for the record of "Helen"
and "John".

Does anyone know how to approach this?


Thank you,
Tom
Click to expand...
Click to expand...
 
Back
Top