Sum column of calculated data on sub form in a module?

[geralf]

Hi,
I have a sub form which has a column with calculated data. How do I calculate the sum of a column in a subform in VBA? I need the value for other calculations in a function. Seems meaningless calculating the values in the sub form all over again when I have them in the sub form already.

Thanks in advance.

Gerhard

 

[chrismcbride]

If you have a sub form that has, say, a Price field and a Units Ordered field. Then you have a calculated control for the value which has a controlsource = [unit_price]*[units_ordered].
Then you put another control in the footer of the sub form and set its controlsource to = Sum([unit_price]*[units_ordered])

You can now reference that value in functions...

HTH
Chris

 

[geralf]

Thanks for your reply Chris!

I already have a contrpl in the footer of the subform which holds the sum of the column. However - when I try to reference this from the function, the value is not there, it's empty. When I use =Sum([CalculatedFields]) and run the form, the sum has a slight delay (doing the calculations I suspect), and then the VBA code has already been run. I cant sql them in either, since they are calculated and not stored in tables. Any other thoughts/ideas?

Thanks
Gerhard

 

[Rich]

Have you tried
set Rs As Form.RecordsetClone
SomeValue =Me.MyTextBox

 

[geralf]

HI Rich!

Thanks for your idea, but I'm using a standard module, not a form module, but hey somethings wrong here. If I have already calculated the controls once, and want to use this,then I can't use a form module. If I want the values when the form is closed, I need to do the calculations when I need them. I'll look into my scheme again, and see what the customer says abot when these data should be available. If I still have problems I'll be back.

Thanks for the help!

Regards
Gerhard

 

[Rich]

A query seems to be the obvious answer without any futher details