Help with formatting a Dlookup with 3 criteria including a long, string & date. (1 Viewer)

MarkK

bit cruncher
Local time
Yesterday, 17:53
Joined
Mar 17, 2004
Messages
7,869
Domain function are not transaction aware. If you do DBEngine.BeginTrans, add a few records, and then run a DCount(), the records you added in the pending transaction will not be counted, not until you run DBEngine.CommitTrans.
 

HillTJ

Registered User.
Local time
Yesterday, 14:53
Joined
Apr 1, 2019
Messages
531
Pat, i am doing exactly that. I call the dmax from a query!. The query is the basis of the 'many' subform for customer quotes. I pass the QuoteID from the main form to the query to return only the records with the QuoteID of the main form. It is unlikely that this subform would contain more than a dozen lines.

But, the Query referred to in the 'dmax' could contain several thousand records! And will grow as more products are added. If i called @MarkK's routine in post 10 instead each time, is that better?.

I'm very keen to adopt 'best practice' & keen to learn as i'm very likely to encounter similar situations in the future. When i started out, i was doing most of the calcs on the form! (Pretty much frowned appon for good reason). The database worked this way for several years without a glitch, but now it is up for a rewrite to add additional functionality.

Ps. I had a bucket load of embedded images too! So i have learn't something along the way!

Appreciate your time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:53
Joined
Feb 19, 2002
Messages
34,426
It doesn't matter. A query is a query. When you run thousands of queries inside a loop or another query. the process can easily bog down. I don't know whether it is faster to do the domain function or call the user function. Test them both.

The best solution is to avoid the functions entirely. If the form does not need to be updateable, you can create a totals query and join to that. That is the most efficient way to get the totals. However, the con is the form is no longer updateable.
 

HillTJ

Registered User.
Local time
Yesterday, 14:53
Joined
Apr 1, 2019
Messages
531
Pat, makes sense. Will give it a go. Thanks
 

Users who are viewing this thread

Top Bottom