Calculated Field in Combobox

[confusedlilly19]

Hi there! I have learned some pretty advanced Access all in the past few weeks, and my brain in just on overload now! I have a calculated field called "Net Electrical Usage" (I have about 12 calculated fields). I want to have a combobox on my frontend form that shows the FIELD NAMES as the options, and when you click on it, it brings up the calculated field value.



I do not know if this is possible, or a way around it. Do I need another table with a list of those TOTAL categories, and then link the tables together in some way to get the total amount to show up on a report?

The combobox would fit into a list of 7 cascading combo boxes on the front end and have to not only find that information but filter off the other comboboxes that reference other tables.

Please let me know how to do this, or if it is possible. I am stuck.

Thank you.

[Rod]

Hi Lilly,

Your requirement, if I have understood you correctly, is both feasible and straightforward as long as the values that comprise your calculated total (or the result itself) are still available at the time the Field Names combo box is clicked. You simply have to retrieve the values from wherever they occur and perform the arithmetic. However from what you say I guess it is not possible to use a calculated field on the "front end" form to achieve this; well, maybe possible, but it would mean burying code into form control properties and confusing everyone, including yourself.

Calculated fields are fine when all the element values are displayed on the same form or report containing the calculated field. The usual reason for using them is that the result is transitory (or not persistent); it is of interest only for this instance. A classic example is extending the total costs on each line of an invoice. In your case I think you need to calculate (= some VBA code) or retrieve the previously stored result and then poke it into some form of display, probably a text box but could be a pop-up, etc.

My first-cut ideas pertaining to the decision whether to recalculate are given in the table at the end of this post. If your form containing the "Net Electrical Usages" is loaded at the time you click on the combo box in your "front end" form (doesn't matter whether it's visible or not, just has to be loaded) then you can simply retrieve the appropriate result directly from the form. (Think it will work for a calculated field - never tried it myself.)

Let me know if you need more help

Recalculate	Must recalculate if any of the element values could have changed in the interrim.
	Recalculate if the calculation is simple and the element values are easily obtainable and you don't want to keep the result for other purposes.
	Recalculate (or pedantically: calculate for the first time) if the prior step may be skipped resulting in no opportunity to store the result.
Retrieve stored result	All situations except the above.