Microsoft Access, Calculations - Text Box Control Source vs VBA Functions

Question

I need opinions from others concerning the best approach.

I have a form and a report that are based on an already created and printed form (not access form). This access form and report have close to 100 boxes that numerical values are assigned to based on a handful of numerical values from a table. The remaining text boxes are calculations based on these handful of numerical values. My original plan was to enter in the calculations into all the control sources of the text boxes. So the control source of Box13 would be the values of Box2 + Box5. Although this worked okay on the form, I now have a report as well with seven different subreports (to allow seven seperate distinctly different pages) that have these same boxes. Now I need to go through and set each box to reference not only the box but also the subreport this box resides on.

The solution I am thinking is to put all these cacluations into vba functions. Then the control sources for the text boxes on both the form and reports would just reference these VBA functions. So instead of the control source of box13 being Box2+Box5 it will be Function Box13. and so on and soforth.

This would allow easy maintenance if calcs where to need to change in the future and potentially make everything easier to understand.

What are your thoughts and ideas concerning my two options. It is always great to hear opinions of others, so I appreciate any and all feedback.

Thanks,

I don't think I understand the question. You have a paper form where people write in the results of calculations and you're storing the data the people input, but you don't really need to store the calculations. In the past when I've done this, I've included data input and checked it against the calculation, to insure that it was calculated correctly. But I wouldn't store the value from the form. Keep in mind that if the calculate value is 13 and the calculated value on the form is 14, it could mean a mistake in the calculation or a mistake in the numbers used for the calculation. — David-W-Fenton

Dan Dan · Accepted Answer · 2011-07-17T14:15:05

An improved 'option 1':

design one table to store the data directly entered by the user
design a query to contain all data from the form and the calculated fields
use this query as data source for the report

The VBA approach should also work fine, but I would not use it in this situation. My .02$ :-)

Microsoft Access, Calculations - Text Box Control Source vs VBA Functions

1 Answers