Thanks for your replies.
Have done what you suggested and put a Me.Recalc in controls and made
Controls source for the ExtendedPrice a calculation. Have also set the
Quantity to decimal as quantities are not always integers.
Now though I'm having trouble with my Main form Orders Total control. Its
control is =Sum([ExtendedPrice]) but now the controls source has changed to
an equation it does not calculate. Do i just put =Sum([CCur=.........etc])?
It sounds like you are having issues which are more related to the
syntax of referring to subform controls from the main form. The main
thing which you need to do is to rename your controls to something
DIFFERENT than the fields of the underlying recordset in order to keep
from pulling out your hair over issues such as these <g> (... as early
as Access 2, which had serious bugs in this respect, I started doing
this to keep my sanity).
For example, let's assume you have a subform called "MySubForm" and a
main form called "MyMainForm" (not very original, but good enough for
now). When you add the subform to the main form, e.g. by dragging it
from the database window onto the main form in design view, Access
gives it a name automatically which is the SAME as the name it has in
the database window. After you have added it like this, click on the
subform in design view, display the "Properties" popup window, and
give it some other name such as "sfMySubForm" or maybe
"ctrl_MySubForm". The form OBJECT in the database window should retain
the same name it had before, but now you have a distinct name for the
form control on the main form which CONTAINS that form as a subform.
The same logic applies to all the fields in the underlying recordset
of the subform, and for all of the records of the underlying recordset
of the main form.
So if you have a calculated expression called "ExtendedPrice" in the
query for the subform's record source, name the control on the subform
"txtExtendedPrice" (for example) which has "ExtendedPrice" as its
control source.
In your main form, let's say you have a control which should display
the total for that calculated value over all the records displayed in
the subform for the current record of the main form. The control
source for that control (regardless of how you name THAT control; you
seem to have [Orders Total], which is just fine) would be:
=Sum([ctrl_MySubForm]![Form]![txtExtendedPrice])
Note how you have to tack on the "...![Form]!..." bit in between the
two control names.
Hopefully, after you substitute the real names for your subform (and
your renamed subform control on the main form), your problem will be
solved! It might be that you need to put that control in the main
form's footer section, but I'm not real sure about it.
[And if John W. Vinson is lurking here, from whom I learned all of
this in the first place
-- and if he sees an omission on my part --
John, please feel free to set me straight!]
And is there any now for the ExtendedPrice that is calculated to be sent
automatically to the OrderDetails (subform) table?
Use the BeforeInsert event of your main form (perhaps for the subform)
if you need to store that calculated data. At any rate, you should NOT
store it twice redundantly! If the underlying data for the calculation
doesn't change, you will be better off just displaying the result of
the calculation instead of storing it in the table. If it DOES change
(e.g. discount rates tend to change with time), then you will need to
store the totals as a record of what was actually charged on the bill
(preferably in a different table than the ones used for capturing the
order data.)
