Conditional Formatting: Comparing fields on different records

[CharlieT302]

Hi All,

I need to perform Excel-like conditional formatting. If a field value changes from the previous record, I need the field to display in a color. If the value is unchanged, no formatting is applied. Conditional formatting (Access 2007 and earlier) does not provide this feature.

I thought, however, I could create an additional calculated field in the query (or even on the form itself) that would compare the target field to the same one on the previous row.

If changed, the calculated field could display the value: 1. If the value is the same, display a zero. I could then base my conditional format on this calculated value. Ex: If 1, change background color. Otherwise, do not.

The actual field value is irrelevant. The only thing of importance is whether it is the same as the row above it. Open to suggestions. The simpler, the better.

Any ideas?

 

[dhookom]

In Access, the "row above it" depends on the sorting of the records in the form. Can you share the Record Source of the form? I expect you might be able to add a column in the query that displays a column value from the previous record if "previous" can be defined.

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

Dhookum,

Actually, the data source is a query that consists of foreign records and, therefore, does not have a primary key. Its purpose it to track changes to a specific table. A record's state is captured before it is altered and again after it is altered. This allows us to monitor specific field values. In the form list, we want a field to alter its color only if its value has changed.

I thought of creating an AutoNumber in the table which would assign a unique number to each record. I could then add a calculated field to the query, using a Dlookup function, to lookup the value from the previous record by adding a criteria to search for a record with an autonumber 1 less than the current. I could then apply the conditional format to the calculated field. I would do this for each field we are tracking. I just do not like using AutoNumbers if I can help it.

Do you know of a better way, or is the Dlookup the most practical approach?

 

[dhookom]

I don't like using DLookup() in queries.

You haven't provided defined how you would determine "previous record".

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

Actually, I did. If every record has a unique value, a Dlookup function can look up a field value (from any field) from the record whose unique value is one less then that of the current record.

Ex: =Dlookup("[LookupField]","DataSource","[ID_Field]="&[ID_Field]-1")

My Question is whether there is a better method for having the ability to examine a field's value between two adjacent records, and format the value if it has changed from the prior record.

 

[dhookom]

I wasn't aware that you actually added a unique ID field or provided any table or field names.

I typically use a subquery like the following which compares the Freight values in by CustomerID in the Orders table in the Northwind sample database:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID, Orders.Freight, 
(SELECT TOP 1 Freight FROM Orders O
 WHERE O.CustomerID = Orders.CustomerID and O.OrderDate < Orders.OrderDate
 ORDER BY O.OrderDate Desc, O.OrderID) AS PreviousFreight
FROM Orders
ORDER BY Orders.CustomerID, Orders.OrderDate;

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya CharlieT302 . . .

A function can be used as an expression in your conditional format. It would work as follows:

The function will be passed a form object and a pk value.
A recordsetclone is made of the form and FindFirst finds the pk. We hold the pk value.
We then moveprevious in the recordsetclone to get the previous value.
We compare the two values and set the return value of the function true/false approriately.
The recordsetclone is set to nothing. This is all per call of the function by conditional formatting.

[blue]A word about MoveNext/MovePrevious in a recordset:

In a recordset there is always a ranking of records independent of sort order. From top to bottom its the 1st to the last. How else would one know where the focus was going when executing MoveNext/Move Previous. It is this inherent ranking so many are talking about.[/blue]

dhookom ... thats one explanation.

So what do you think CharlieT302?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CharlieT302]

Hey Aceman1

Thanks for the input. Conceptually, this sounds like what I have been searching for. Can you give me an example of what the expression would look like? Is it created in the Conditional Formatting dialog box?

 

[TheAceMan1]

CharlieT302 . . .

Sorry to get back so late and yes the function with a conditional is entered in the Conditional Formatting dialog box. We'll get to this in a minute.

First a few details on the function.
[ul][li]It needs to resides in the code module of the form.[/li]
[li]The example uses a form with three fields [blue]ID[/blue] (primarykey), [blue]actName[/blue] (a name field), [blue]Amount[/blue] (a currency field). However only the [blue]ID[/blue] and [blue]Amount[/blue] are used in the function.[/li]
[li]The [blue]ID[/blue] is needed to pindown the record conditional formatting is parsing (note: a call to the function is made for each record.)[/li]
[li]The [blue]Amount[/blue] is simply the field used to compare current to previous values.[/li]
[li]For your own form just change the names of [blue]ID[/blue] and [blue]Amount[/blue]. [blue]ID[/blue] also has to be changed in the Conditional Format expression.[/li][/ul]
Here's the function:

[blue]Public Function Differ(ID As Long) As Boolean
   Dim rst As DAO.Recordset, hldAmt As Currency
   
   Set rst = Me.RecordsetClone
   
   If rst.RecordCount > 0 Then
      rst.FindFirst "[ID] = " & ID
      
      If Not rst.NoMatch Then
         hldAmt = rst!Amount 'hold the amount at the ID found
         rst.MovePrevious 'step back one record
         
         If Not rst.BOF Then
            If hldAmt <> rst!Amount Then Differ = True
         End If
      End If
   End If
   
   Set rst = Nothing
   
End Function[/blue]

Now in design view select your comparison field ([blue]Amount[/blue] in the sample) and open the Conditional Formatting dialog. Sekect expression in the combobox on the left then enter the following expression in the dialog:

[blue]Differ([ID])=True[/blue]

Can you see it! ...

A sample db can be downloaded here: ConditionalFormatDB

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CharlieT302]

Hey Aceman,

Thanks for getting back to me. If I am reading this correct, the ID is necessary only to locate the "current" record within the recordset. The code then moves backward to the previous record; regardless of its ID number, to read the field that is being examined. In your example, this would be "Amount".

It then compares the "amount" value of the prior record to that of the current record. Correct? If so, that is great.

You said the code needs to reside in the code module of the form. I understand event code. How do I access to form's code module?

 

[TheAceMan1]

[blue] ... the ID is necessary only to locate the "current" record within the recordset.[/blue]

Loosely stated thats correct. To nail it the statement would be ... the ID is necessary to locate the "current" record [purple]being parsed by conditional formatting[/purple] within the recordset.

[blue]It then compares the "amount" value of the prior record to that of the current record. Correct?[/blue]

Yes ... that is correct. Note that I hold the amount of the current record once its found with [blue]hldAmt = rst!Amount[/blue]. Then move previous and compare.

[blue] How do I access to form's code module?[/blue]

There are two types of modules ...
[ul][li][blue]Standard Modules[/blue] which reside in the [blue]Modules[/blue] window.[/li]
[li][blue]Class Modules[/blue] which reside with forms (sometimes referred to as code behind forms).[/li][/ul]
Where talking the [blue]class module[/blue] behind your form, and there are several ways to get there:
[ul][li]Hot keys [blue]Alt + F11[/blue][/li]
[li]The [blue]Code[/blue] toolbar button[/li]
[li]Thru clicking the three elipses ([blue]...[/blue]) of any event on the events tab.[/li][/ul]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CharlieT302]

Aceman,

Thank you very much for this. This should be a great help to many. I will try putting this in effect later today and let you know if there is a problem. This should solve a lingering "possible" problem I have been trying to solve that comes from the Dlookup method.

Thanks