Controlsource Issue 2

[georgesOne]

Good morning,

I have 4 textboxes to run the sum of four fields in the same table:

tbxSumSolid=DSum("[Solid%]","tbl_FormulationDetails","Formulation = '" & Forms!frm_Formulation.tbxFormulation & "'  And Active = True")
tbxSumSolvent=DSum("[Solvent%]","tbl_FormulationDetails","Formulation = '" & Forms!frm_Formulation.tbxFormulation & "'  And Active = True")
tbxSumToUse=DSum("[ToUse]","tbl_FormulationDetails","Formulation = '" & Forms!frm_Formulation.tbxFormulation & "'  And Active = True")
tbxSumActual=DSum("[Actual]","tbl_FormulationDetails","Formulation = '" & Forms!frm_Formulation.tbxFormulation & "'  And Active = True")

As can be seen the code is almost the same for all four fields. All have the same datatype and are indexed.
However, the first and the last calculation are fast, while the second and third calculations run terribly slow...
Any tip and/or explanation?

Thanks, Georges

 

[georgesOne]

... may be, my previous post is somewhat unclear.
For textbox tbxSumSolids as shown above, I can either use VBA code like:

tbxSumSolids.ControlSource = "=DSum('[Solid%]','tbl_FormulationDetails','Formulation = Forms!frm_Formulation.tbxFormulation And Active = True')"

or use the properties box and build the controlsource expression

=DSum("[Solid%]","tbl_FormulationDetails","Formulation = '" & Forms!frm_Formulation.tbxFormulation & "'  And Active = True")

So the code is not the problem, but it takes a long time until the calculation is done.
Is there any way to speed this up?

Thanks, georgesOne

 

[MajP]

Try building a seperate aggregate query. You should be able to build a query that sums up each of these fields and use a where statement like

Where Formulation = [Forms]![frm_Formulation].tbxFormulation And Active = True

Now instead of dsums use dlookups and since the query returns a single record you would not need a criteria. Like:

dlookup("[Solid%]","qrySums")

If this is still slow you will have to write your own "dlookup" using recordsets.

 

[MichaelRed]

DSum (and ALL of the "Domain Aggregate" intrinsic functions in MS are just wrapper functions which generate the query (or queries) a-la Maj-P' response.

If at all possible, use some form of direct query to get information out of the data (tables).




MichaelRed

 

[georgesOne]

Thanks a lot... that worked great.
Also exactly the background information amateurs like me need.

Regards, georges