Help with formatting a Dlookup with 3 criteria including a long, string & date.

[MarkK]

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]

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]

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]

Pat, makes sense. Will give it a go. Thanks