Speed Effeciency on Form updates

  • Thread starter Thread starter KM
  • Start date Start date
K

KM

Question on overall Form design
Have a lot of data on a financial form, 20 rows, 8+ columns
In addition, delta columns are present and math on these can change
depending on the user choices.

Question:
Is it more effecient to us VB to calculate column differences and stuff the
results into the form fields or is it better to use controlsource property
and set the formula within the field and let the form calculate it?
 
The answer depends on what the calculations are, and how many disk reads,
recordset opens etc are involved in them.

In general, it's best to do anything that SQL can do first, such as joins,
aggregation, criteria, subqueries, queries stacked on each other.

Once you hit a wall with SQL, you can start thinking about a VBA solution.

If that gets too inefficient also, you will need to consider using a
temporary table to store the partially massaged data for further processing.
 
hi,
Is it more effecient to us VB to calculate column differences and stuff the
results into the form fields or is it better to use controlsource property
and set the formula within the field and let the form calculate it?
Click to expand...
As Allen wrote, using SQL should be giving you the best results.

Don't use the domain functions (DLookup(), DSum(), etc.). In many cases
you can use an appropriate INNER JOIN and a GROUP BY. In some cases you
can't avoid them. Then use the replacement functions:

http://allenbrowne.com/ser-42.html


mfG
--> stefan <--
 
Back
Top