Conditional formatting issue

[murgatroyd]

I have a form and a report in Access 2010 that both do essentially the same thing: they both use the same query as a data source, and they both list the same fields.

I have added conditional formatting to several of the fields in both the form and the report, and this works in both the form and the report, except for one field, where the same conditional formatting expression works in the form but not in the report. (It's not a typo, as I copied/pasted the conditional formatting expressions from the form to the report.)

Expression is
[OrderType]=2
And [Size]>0
And [Size]<=DLookUp("Threshold","dbo_Parameters")
And [Level]=1
And [Item]=DLookUp("Item","dbo_Job","Order=[Index] And JobType=3 And JobStatus=5")

How can I get this to work in the report as it does in the form?

(The issue is not in the data, which contains records that trigger the conditional formatting correctly in the form but not at all in the report. The issue appears to be with the DLookUp function in the last term, because if I remove that term, then the rest of the expression triggers the conditional formatting correctly in the report.)

 

[Galaxiom]

The DLookup will return the first match it finds.

It would be far better to join the tables and calculate the conditional format information in the RecordSource query. Then the format condition becomes trivial.

 

[murgatroyd]

Thanks for your reply. Yes, DLookUp returns the first match it finds. In this recordset, this DLookUp term will find a unique match for each record, so that is not a factor.

The issue is that I have a form and report that both have the same conditional formatting expression; however, it triggers correctly in the form but not at all in the report.

 

[arnelgp]

further to mr.galaxiom's suggestion, include the Dlookup calculated field in your report, as hidden control, then test the condition including this new control.

on your query:

Threshold: DLookUp("Threshold","dbo_Parameters")
dbo_jobLookUp("Item","dbo_Job","Order=[Index]DLookUp("Item","dbo_Job","Order=[Index] And JobType=3 And JobStatus=5")

add threshold and dbo_job fields in your report as hidden textbox control and include these in your criteria in conditional format.

 

[murgatroyd]

Thanks for your reply. I will try your suggestion.

Is there a technical reason that my conditional formatting expression works in my form but not in my report?

 

[CJ_London]

Is there a technical reason that my conditional formatting expression works in my form but not in my report?

possibly - both order and index are reserved words, Using them can result in unexpected errors or results - which might explain why it works in a form environment and not in a report

see this link

https://support.office.com/en-us/ar...da237c63eabe?ui=en-US&rs=en-US&ad=US&fromAR=1

 

[murgatroyd]

Thanks for your reply. I used fictitious field names to make it easier to follow; looks like I should have picked different ones!

Is there a technical reason for a conditional formatting expression like this to work in a form but not in a report?

Expression is
[OrderType]=2
And [Size]>0
And [Size]<=DLookUp("Threshold","dbo_Parameters")
And [Level]=1
And [Item]=DLookUp("Item","dbo_Job","OrderRef=[Service] And JobType=3 and JobStatus=5")

 

[CJ_London]

that was my offering - no technical reason I'm aware of, but personally I would follow arnelgp's suggestion. dlookups are slow at the best of times - so as an alternative why not just add dboparameters to your query and reference the field directly

 

[Galaxiom]

personally I would follow arnelgp's suggestion. dlookups are slow at the best of times - so as an alternative why not just add dboparameters to your query and reference the field directly

Except arnelgp is still using a DLookup. This logic could all go into the RecordSource query.

Maybe it works on a single form because it only needs to lookup one record at a time and not in the report because it chokes on the vast number of lookups.

BTW this looks unlikely if you are expecting to return one record:

And [Item]=DLookUp("Item","dbo_Job","OrderRef=[Service] And JobType=3 and JobStatus=5")

Did you mean to refer to the Service field in the current record?

And [Item]=DLookUp("Item","dbo_Job","OrderRef=" & [Service] & " And JobType=3 and JobStatus=5")

 

[murgatroyd]

Thanks for your further reply. Using DLookUp for the conditional formatting was arnelgp's suggestion in another thread, because getting the data needed for the conditional formatting from the query required some table joins that made some fields on the form uneditable.

DLookUp is a tad slower but acceptable.

Did you mean to refer to the Service field in the current record?

And [Item]=DLookUp("Item","dbo_Job","OrderRef=" & [Service] & " And JobType=3 and JobStatus=5")

Ah yes, that was the problem, thanks muchly.

 

[CJ_London]

some table joins that made some fields on the form uneditable.

either all or none of the fields would be editable, Just out of curiosity - did you try changing the recordset type to 'dynaset - inconsistent updates' - often works when linking in 'lookup' fields to a main table for editing

 

[murgatroyd]

I just tried adding back the extra tables to the query to get the data for the conditional formatting, and there was no noticeable difference in performance between this and DLookUp, but with the recordset type set as "Dynaset (Inconsistent Updates)", the uneditable fields became editable. I haven't come across this option before. What is the downside of this? (Presumably there is one, otherwise the option wouldn't be needed?)

 

[CJ_London]

use with care - it is used where you have a form recordsource of more than one table which makes it unupdateable but you are only editing data in one table. SOemtimes recordsource is so complex even this option doesn't work