Conditional formatting if text box matches most recent

Gladman

Registered User.
Local time
Today, 21:49
Joined
Aug 9, 2014
Messages
17
Hi All,

I have a form and I am trying to highlight all values that equal the top value. I recently learnt that I can create a query style record in this thread and thought I can apply the same principals.
https://access-programmers.co.uk/forums/showthread.php?t=291097

So in the conditional formatting wizard I created;

[qryEmployee].[Field1]=(SELECT TOP 1 Dupe.Field1 FROM qryEmployee AS Dupe WHERE qryEmployee.EmployeeID=Dupe.[EmployeeID] ORDER BY qryEmployee.DateAndTime DESC)

This uses the expression but I have tried Field Value Is (SELECT Top 1 .......)
qryEmployee groups all the data from tables required in the form.

The conditional formatting doesn't work but I have created a query (and removed the Dupe reference and filter the EmployeeID manually) where it shows the matching field I want it to. Is there an easier way to highlight a textbox if it matches the most top value in the form?

Thanks
 
you can't use sql code in this way. you need to use the domain functions - think the dlookup in this case or perhaps dmax.

Or alternatively (and probably significantly faster) to include your query as part of the recordsouce and have a calculated field in your recordsource that returns true of false depending on the match.

The field does not need to be assigned to a control so will not appear on the form. Your conditional format them becomes

expression is....[calcfield]=true

you may need to change the form recordset type from dynaset to dynaset - inconsistent updates
 
Hi CJ,

Thanks for the quick reply. That has done exactly what I want however it is really slow and I have to click the field to motivate the formatting to change. I assume (and will try now) that appending qryEmployee to a table will improve the speed.

Here's what I have in the conditional formatting window;

Field Value is; equal to; DLookUp("ProductGroup","qryEmployee", "EmployeeID=" & [EmployeeID])

Is there anything you can see that will make the form run slower? I tried Dmax and that seemed to be worse.

Many thanks
 
Just created a table and it worked perfectly :) Thank you again CJ.

EDIT;

My revised equation is now;
DLookUp("ProductGroup","tblEmployeeForm", "EmployeeID=" & [EmployeeID])

Now I just need to add some code to update this table when I open the Form :)
 
domain functions are slow, so I was suggesting you add the query to your recordsource - something like

SELECT myTable.*, myTable.Field1=myQuery.Dupe as isMax
FROM myTable INNER JOIN myQuery ON myTable.EmployeeID=myQuery.EmployeeID

myQuery being (I suspect) your qryEmployee

then your conditional formatting would be

Expression Is.....[isMax]
 

Users who are viewing this thread

Back
Top Bottom