Conditional formatting - from another table (1 Viewer)

Gismo

Registered User.
Local time
Today, 17:20
Joined
Jun 12, 2017
Messages
1,298
Hi,

i need to highlight a control if a value is met from another table
i used expression:# [DAW Sheet Data File]![Raise CS Order]=2#
but does not highlight.
any reason why?

1590410899785.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:20
Joined
Sep 21, 2011
Messages
14,044
Just how is CF meant to be able to look into a table?, for what particular record? :unsure:

Bring the data into the form, then address that control in the CF rule.
 

Micron

AWF VIP
Local time
Today, 11:20
Joined
Oct 20, 2018
Messages
3,476
It is possible with DLookup but I agree - bring the data into the form. If more than one record could satisfy the DLookup function then the value returned by it would be random.
EDIT - BTW, not only might multiple records satisfy the lookup, the criteria probably needs to be based on a form field on the current record. If not, the DLookup return will be constant for every record; i.e. if it is true at all, it will be true for any record on the form.
 
Last edited:

Gismo

Registered User.
Local time
Today, 17:20
Joined
Jun 12, 2017
Messages
1,298
didnt think this one through
works fine

another question on conditional formatting

is it possible to have conditional formatting to display a text if a pertain value is met?
 

Micron

AWF VIP
Local time
Today, 11:20
Joined
Oct 20, 2018
Messages
3,476
Depends on what you mean by that. Display a text in some control if a format is applied to some control? If using the conditional format dialogs I'd say no, unless someone can come up with an expression that would work. I can't think of one at the moment. If you wanted to do it all in code, then should be doable if you test a format property and then set the value accordingly, but you seem to be going about things in a very odd way.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:20
Joined
May 21, 2018
Messages
8,463
You only need conditional formatting if the form is in continuous view. This can be done simply by code if single form view.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Feb 19, 2002
Messages
42,970
NEVER use dLookup() in queries. Create a query that joins to the lookup table. This is extremely inefficient. Use a left join if the lookup value is optional otherwise, you will loose records where the lookup value is null.

The join allows you to easily show data from multiple tables on one form. There is a caveat though. When you display lookup data, you should set the locked property to yes to prevent accidental updating. For example, on an order entry form, you want to show the customer name and possibly contact information. If you don't lock the name and contact info, someone looking at the order form might accidentally change Acme Tools to Oracle and that would actually change the customer record. Could be a nightmare to fix so just lock the lookup values to prevent accidental updating.
 

June7

AWF VIP
Local time
Today, 07:20
Joined
Mar 9, 2014
Messages
5,423
Conditionally display text with expression in textbox ControlSource. Use IIf(), Switch(), or Choose().
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:20
Joined
Feb 19, 2002
Messages
42,970
@Micron,
dlookups() run queries so when you run them inside another query, you run a separate query for every row of the original query. If the outer query returns a thousand rows, you run 1001 queries. The original plus the lookup query for each row.

In almost all cases dlookups() can be replaced with outer joins. Using a Left Join caters for the situation where the lookup value might be null. If you were to use an Inner Join, only rows with matches in BOTH tables would be returned.

Running dlookup()s in a code loop seems to be even worse. I ran into this very early on in my Access career. I came from a COBOL environment so I already knew how to code and so I created a procedure exactly like I would have built in COBOL to handle a two file match. My main table had 190,000 rows. The procedure took 1.5 hours to run. The loop included two lookups for each row. So as part of the process of trying to figure out what was so wrong with the code, I commented out the 2 lookups and the procedure took 8 minutes. The lightbulb went on and and I changed the procedure to use a query with two left joins and that reduced the time to around 9 minutes. It was a pretty dramatic improvement. I have the whole thing more thought and decided that I could actually do the whole thing in an update query and the final result was 3 minutes.

So, the bottom line is don't use dlookup()s inside ANY loop (query or code) unless there is no other alternative and don't use a code loop when an action query will do the job.
 

Micron

AWF VIP
Local time
Today, 11:20
Joined
Oct 20, 2018
Messages
3,476
@Pat, I know that. Can you show me where it was posted here that a DLookup was in a query? I don't think you can, which is why I asked if your comment was general in nature or if it was stated, then I missed it, hence I asked that question.
 

Users who are viewing this thread

Top Bottom