form sum - subtotal, shipping, and total cost

[bricklebrit]

Hello,

I'm trying to create a form that contains a subform to create the subtotal, shipping, and total cost in the main form.

Could anybody direct me on how to do this - I keep getting #error!

My values should be:

Subtotal should be =Sum([price]) from the subform OrderDetails

Total should be =Sum(Sum([price]+[FreighCharge]))

the [price] value is from the subform [orderDetails] and the [FreightCharge] value is preset in the main form [Orders3]

Any possible suggestions on how I could achieve this are appreciate!

Thanks in advance,
Jon

 

[Jack Cowley]

See the articles below....

http://support.microsoft.com/support/kb/articles/Q209/0/99.ASP?LN=EN-US&SD=gn&FR=0&qry=subforms&rnk=5&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

http://support.microsoft.com/support/kb/articles/Q119/1/34.asp?LN=EN-US&SD=gn&FR=0&qry=q119134&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

 

[bricklebrit]

Hi Jack,

Thanks for the links. I've been reading over them, but I am having a problem getting started. I'm still having difficulty getting the syntax correct.

I'm a beginner at this and I'm having problems even getting started with the subtotaler.

If you could help me get started, I think I could figure out the rest. I can get the subtotal to add up the sums, but I'm not sure how to assign this value to the original [OrderDetails] subform.

So far, on update of the [seriesTitle] it pulls data to the [price] field.

uponUpdate of the [price], I would like it to setValue [Subtotaler] to =sum([price])

If you have any thoughts on how to do that or get me started, it would be appreciated. Once I get the hang of it, I'm sure I can get the rest.

Thanks in advance for your help.
Jon

 

[Fi]

try this Jon =Sum([Price])+Sum([Forms]![Orders3]![FreightCharge])

Fi

 

[bricklebrit]

Hi Fi,

Thanks for the assistance, but I'm still getting an #Error

The actual addition I want to do is in the [Orders3] form and I want to add:

Sum([Price]) - from subform [OrderDetails]
[FreightCharge]- from the same form [Orders3]
[PriceAdjustment] - from the same form

and have all those values go into the field:
[totalOrderCost] which is the order3 form all the addition is taking place in.

For the life of me, I can't seem to get the syntax quite right. Any thought you may have are appreciated!

Thanks,
Jon

 

[Fi]

Hi Jon
In the control source of the cell you are using to calculate 'TotalOrderCost 'in try this
=Sum([Forms]![OrderDetails]![Price])+Sum([FreightCharge])+Sum([PriceAdjustment])
I am doing something similar and found that if there was no value in one of the cells in the calculation then it would show an error message so I set the default, of each of the cells in the calculation, in the table, to 0 if this fixes it you will need to enter 0 in all the records in the table that have no value, also check the relationship between your main form and subform...good luck

 

[Rich]

We assume the Sum([Price]) is already calculated in the sub form footer and freight cost is a single value as is [price adjustment] both of which are on the main form,is that correct?

 

[bricklebrit]

Hi Fi & Rich,

Thanks for both your help -- you guided me to the answer. The final working syntax to display the cost is:

=[orderDetails].[Form]![OrderSubtotal]+([FreightCharge]+[PriceAdjustment])

One last question is, what is the command for setting this value to the main form's value [TotalOrderCost] ? If I can do that, all is fixed!

Thanks again for all your assistance,
Jon

 

[DBL]

If you mean you want to store the sum to the base table, Access isn't really designed to save dynamic calculations. You have all the components there to create the final calculation and there isn't any real need to save it.

If you just want to show the subform calculation in an unbound field on the main form, use the build button on the field's recordsource to reference the subform field:

Forms![mainformname]![subformname]![subformfieldname]

 

[bricklebrit]

Hi DBL,

Thanks for the response. I was able to create the calculation in an unbound field on the main form, but I was wanting to save it to the base table for other query calculations and totaling to keep track of how much individual customer have spent.

Is there no way to save this calculation's value back to the base table value?

Thanks,
Jon

 

[bricklebrit]

HI DBL,

I've been thinking about it and looking at the database and I now understand your reasonging for having set values for dynamic totals -- I'll just have to go through and rework some of my forms and queries to reflect the dynamic addition.

Thanks for help and insight!
Jon