Conditional formatting - from another table
- Thread starter Gismo
- Start date
D
Deleted member 147267
Guest
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.
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 by a moderator:
D
Deleted member 147267
Guest
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.
- Local time
- Today, 06:14
- Joined
- Feb 19, 2002
- Messages
- 47,102
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.
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.
D
Deleted member 147267
Guest
Is that just general advice or was that suggested or being used here? If so. I missed it.
- Local time
- Today, 06:14
- Joined
- Feb 19, 2002
- Messages
- 47,102
@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.
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.
