Dlookup vs Query

[calvinle]

Hi,

On the main form, I add a bunch of Dlookup textbox to query all the amount, quantity of product of a client. Somtimes, it takes time to load all the information, I was wondering if by creating a query and insert that query straight on the form will be any faster?

Thanks

 

[pbaldy]

Hard to say without the specifics, but my first choice would probably be a totals query populating a subform.

 

[Galaxiom]

Put the textboxes into a bound subform and get all the values with a single query.

You can make the subform look like it is part of the main form if you like by removing the border from the subformcontrol and getting rid of the record selectors etc from the subform Source Object.

 

[sneuberg]

I suspect it would be slightly faster. If the data is all coming from the same table I'd create a subform for it and put the subform on the form in place of the DLookUps. You can change the format of a subform (transparent border, no navigation, record selectors, etc) so that you won't even know it's a subform. The query you talked about of course would be the records source of the subform.

 

[Galaxiom]

I suspect it would be slightly faster. If the data is all coming from the same table I'd create a subform for it and put the subform on the form in place of the DLookUps.

A query is MUCH faster than multiple DLookups which are, in essence, each a query. The query is better even if the data comes from multiple tables, provided a query can be written for the job.

BTW The same rules for speed from queries applies to domain functions. Index the criteria fields in the table etc.

 

[calvinle]

Right now, I have about 16 textbox witha Dlookup in it. The issue with creating the query is that I can never come to 1 query that can query all the information as they come from multiple table.
Is it fine if I use multiple query, then multiple subform in the main form? Will that be any faster? Will it be unprofessional to have multiple query subform on the main form?

Thanks

 

[jdraw]

Can you show us a jpg of your current form?
How many subforms do you plan?
What is the subject matter involved? Some samples may be helpful for readers.

 

[Galaxiom]

Right now, I have about 16 textbox with a Dlookup in it. The issue with creating the query is that I can never come to 1 query that can query all the information as they come from multiple table.

Multiple tables does not prevent querying per se. Depends on the structure.

Is it fine if I use multiple query, then multiple subform in the main form? Will that be any faster? Will it be unprofessional to have multiple query subform on the main form?

Every Dlookup you get rid of helps. As we have said, the controls on the subforms can be made to look like they are right on the main form if you want.

Sixteen Dlookups looks unprofessional.

 

[calvinle]

Can you show us a jpg of your current form?
How many subforms do you plan?
What is the subject matter involved? Some samples may be helpful for readers.

To replace all those textbox, I would need 3 differents query, so 3 new subform on that main form. I can make looks like it embed inside the main form, that's not an issue. I care more about the performance.

I was able to also make 1 query to have all the data, but that will be a query that contains Expression of Dlookup in it.. I heard someone said to not use Dlookup in a query itself to look for other data, so I guess my single query is not properly built?

 

[jdraw]

What is the subject matter involved? What are you trying to do in plain English?
Some samples may be helpful for readers.

 

[Galaxiom]

I care more about the performance.

With sixteen DLookups you are running sixteen queries. Three would be a positive improvement.

I was able to also make 1 query to have all the data, but that will be a query that contains Expression of Dlookup in it.. ?

DLookups are usually only necessary in a query to avoid problems with updateability. I understand you are just reporting purchase history so this should not be an issue. It may need subqueries but I suspect it can be done in one.

Post the SQL of your query.