#error for Sum on calculated subform detail row total

[morleyc]

Hi, I have a subform with a number of rows with combo box lookups.
Hidden fields of the combo box lookups are used to perform a row
calculation, for example:

RowTotal text box control source is set to: [Quantity]*[Type].[Column]
(2)

The row total is calculated perfectly. But when i try and sum this
RowTotal i get an error, i have read Sum() can only be performed on
fields not calculated controls as i am doing, but is there any way to
achieve what i want?

I'm guessing the only option is to do a total query with DLookup but i
was worried about speed and would like to have offloaded this to the
machine rather than doing an additonal SQL call.

Any ideas?

Thanks,

Chris

 

[SusanV]

You can use the on update event of the combobox to populate a variable with
that value, do your calculations then populate your totals box with the
result, something like:

dim i as integer

i = me.combobox.column(2)

me.totaltextbox = me.quantity * i

 

[morleyc]

You can use the on update event of the combobox to populate a variable with

that value, do your calculations then populate your totals box with the
result, something like:

dim i as integer

i = me.combobox.column(2)

me.totaltextbox = me.quantity * i

Hi, are you 100% sure about that, because would it not be that for
each row the totalbox will be overwritten? I want to sum for all
lines, which would be different than the above?

Thanks again,

Chris

 

[SusanV]

Is this a continuous form? If so, then I would not do this with live data
(maybe try it on a backup but then again ALL dev should be done on a backup)