Form calculation is not working

[Big Al]

Thanks for all your help on my other problem with this form. I have one
other problem (at the moment <grin>)

I am on the order form, trying to get the from to add up more than one line
of products, i.e. two different products on the same order.
I have set up a box to accept the sub total and then this information is
transferred from this box to the form in a box Sub total

However, the sub total is not adding up.

The text box Orderdetailstotal is set up as a currency format, and in the
expression builder I have used the following:
=Nz(Sum([Price]))
where I have put in Nz if the total is zero and the column with the sales
price is called Price
Am I missing something?

 

[BruceM]

Your description is unclear. To what subtotal do you refer? To where and
for what purpose are you trying to transfer that information? Are the line
items on the order in a separate OrderDetails table, or are they in the
Orders table? If you are using an OrderDetails subform you can put:
=Sum([Price])
into the subform's Form Footer. If you have some preset number of line
items on the Orders form you will just need to add all of the relevant text
boxes together:
=[Text1]+[Text2] etc. If you are taking this approach you will need to hope
that you never have an order with more items than you have alllowed for.
I don't see a need for Nz, which is used when zero or null would create
problems with the function.

 

[Big Al]

Sorry BruceM.
I have an order form, which has a subform for the individual orders in the
middle of the order form.
What I am trying to do is as you say the line items are in a separate order
details sub form
I am trying to get a total for the order which I then transfer from the sub
form to the main body of the form as a sub total before shipping and tax are
applied.
In the sub forms footer I have =Nz(Sum([Price])) but it does not come
forward with a figure either into the main part of the form or, if I look at
the subform in Form view, in the box in the footer (it is just empty)
The sales Price in the sub form (i.e. the box "Price") is correct but there
is nothing in orderdetailstotal box.
I have tried putting the formula as you have said but still no luck.

Your description is unclear. To what subtotal do you refer? To where and
for what purpose are you trying to transfer that information? Are the
line items on the order in a separate OrderDetails table, or are they in
the Orders table? If you are using an OrderDetails subform you can put:
=Sum([Price])
into the subform's Form Footer. If you have some preset number of line
items on the Orders form you will just need to add all of the relevant
text boxes together:
=[Text1]+[Text2] etc. If you are taking this approach you will need to
hope that you never have an order with more items than you have alllowed
for.
I don't see a need for Nz, which is used when zero or null would create
problems with the function.

Thanks for all your help on my other problem with this form. I have one
other problem (at the moment <grin>)

I am on the order form, trying to get the from to add up more than one
line of products, i.e. two different products on the same order.
I have set up a box to accept the sub total and then this information is
transferred from this box to the form in a box Sub total

However, the sub total is not adding up.

The text box Orderdetailstotal is set up as a currency format, and in the
expression builder I have used the following:
=Nz(Sum([Price]))
where I have put in Nz if the total is zero and the column with the sales
price is called Price
Am I missing something?

 

[BruceM]

Let me see if I understand. You have an Orders table and an OrderDetails
table related one-to-many. You have a form based on the Orders table, with
an embedded subform based on OrderDetails. OrderDetails contains a field
called Price. In the form footer of the OrderDetails subform you have an
unbound text box with the Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?
This should work, so something is not as I imagine it from your description.
Read my questions carefully, and be sure you know the answer to each one.
What happens if you put the text box with the Sum expression into the Detail
section of the subform?
To reference the subtotal text box on the main form you need the control
source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

Sorry BruceM.
I have an order form, which has a subform for the individual orders in the
middle of the order form.
What I am trying to do is as you say the line items are in a separate
order details sub form
I am trying to get a total for the order which I then transfer from the
sub form to the main body of the form as a sub total before shipping and
tax are applied.
In the sub forms footer I have =Nz(Sum([Price])) but it does not come
forward with a figure either into the main part of the form or, if I look
at the subform in Form view, in the box in the footer (it is just empty)
The sales Price in the sub form (i.e. the box "Price") is correct but
there is nothing in orderdetailstotal box.
I have tried putting the formula as you have said but still no luck.

Your description is unclear. To what subtotal do you refer? To where
and for what purpose are you trying to transfer that information? Are
the line items on the order in a separate OrderDetails table, or are they
in the Orders table? If you are using an OrderDetails subform you can
put:
=Sum([Price])
into the subform's Form Footer. If you have some preset number of line
items on the Orders form you will just need to add all of the relevant
text boxes together:
=[Text1]+[Text2] etc. If you are taking this approach you will need to
hope that you never have an order with more items than you have alllowed
for.
I don't see a need for Nz, which is used when zero or null would create
problems with the function.

Thanks for all your help on my other problem with this form. I have one
other problem (at the moment <grin>)

I am on the order form, trying to get the from to add up more than one
line of products, i.e. two different products on the same order.
I have set up a box to accept the sub total and then this information is
transferred from this box to the form in a box Sub total

However, the sub total is not adding up.

The text box Orderdetailstotal is set up as a currency format, and in
the expression builder I have used the following:
=Nz(Sum([Price]))
where I have put in Nz if the total is zero and the column with the
sales price is called Price
Am I missing something?

 

[Big Al]

Let me see if I understand. You have an Orders table and an OrderDetails
table related one-to-many. You have a form based on the Orders table,
with an embedded subform based on OrderDetails. OrderDetails contains a
field called Price. In the form footer of the OrderDetails subform you
have an unbound text box with the Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the answer
to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only think
its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate in
them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and Control
Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and it
DID show the sum of the units price columns totalled!

To reference the subtotal text box on the main form you need the control
source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the column I
want, i.e. the sub total of the piece price X the quantity less the discount

 

[BruceM]

Responses inline

Let me see if I understand. You have an Orders table and an OrderDetails
table related one-to-many. You have a form based on the Orders table,
with an embedded subform based on OrderDetails. OrderDetails contains a
field called Price. In the form footer of the OrderDetails subform you
have an unbound text box with the Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the answer
to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only think
its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate in
them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation itself.
It sounds as if Price is the name of the control in which the calculation is
contained, in which case the unbound text box in the subform footer needs to
be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))

Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and it
DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field, but
not the calculated control.

To reference the subtotal text box on the main form you need the control
source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the column I
want, i.e. the sub total of the piece price X the quantity less the
discount

If this answers your questions, remember that you need to be precise in
framing your question. You kept referring to Price as a Column, which I
took to mean Field, but in fact it seems to be the name of a calculated
control. Remember, we can't see your database, so must rely on your
description.

 

[Big Al]

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate one
order line?
I probably am not making sense, but the idea is that an order sub form can
have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price £20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this case
Prices, i.e. £38

many thanks for your patience on this

Responses inline

Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on the
Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of the
OrderDetails subform you have an unbound text box with the Control
Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only think
its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate in
them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))

Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and it
DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field, but
not the calculated control.

To reference the subtotal text box on the main form you need the control
source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the column
I want, i.e. the sub total of the piece price X the quantity less the
discount

If this answers your questions, remember that you need to be precise in
framing your question. You kept referring to Price as a Column, which I
took to mean Field, but in fact it seems to be the name of a calculated
control. Remember, we can't see your database, so must rely on your
description.

 

[Big Al]

Actually, I have just answered my own question.
Many thanks for getting me out of this mess

One final question. I am using Access 2003.
Each time I open a database I get a security warning dialog box pop up
stating "security warning, unsafe expressions not blocked" and asking me to
either block unsafe expressions or not.
when clicking on no I then get another security warning telling me that the
file may not be safe if it contains code that was intended to harm my
computer and then asks me is I want to open the file. I have to click on
open to get the database to load.
is this normal? or is there a way to stop these two dialogue boxes from
appearing?

TIA

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate one
order line?
I probably am not making sense, but the idea is that an order sub form can
have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price £20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this case
Prices, i.e. £38

many thanks for your patience on this

Responses inline

Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on the
Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of the
OrderDetails subform you have an unbound text box with the Control
Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only
think its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate
in them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))

Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and
it DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field,
but not the calculated control.

To reference the subtotal text box on the main form you need the
control source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the column
I want, i.e. the sub total of the piece price X the quantity less the
discount

If this answers your questions, remember that you need to be precise in
framing your question. You kept referring to Price as a Column, which I
took to mean Field, but in fact it seems to be the name of a calculated
control. Remember, we can't see your database, so must rely on your
description.

 

[BruceM]

Glad to hear you got it working.

Google Groups can be a great help in such cases as the error message. I
searched for the error message, and found the following )posted by Joan Wild
when somebody asked the same question):

You can go to the Tools, Macros, Security and set it to low.

FAQ on macro security in Access
http://office.microsoft.com/en-us/assistance/HA011225981033.aspx


If the security item is missing from the menu, just reset your Menu Bar.

I should say that when I tried to check that setting it wasn't there, and I
couldn't reset the toolbar, but that's as far as I can go with that. I
would suggest a new thread if you have questions on that particular subject.

Actually, I have just answered my own question.
Many thanks for getting me out of this mess

One final question. I am using Access 2003.
Each time I open a database I get a security warning dialog box pop up
stating "security warning, unsafe expressions not blocked" and asking me
to either block unsafe expressions or not.
when clicking on no I then get another security warning telling me that
the file may not be safe if it contains code that was intended to harm my
computer and then asks me is I want to open the file. I have to click on
open to get the database to load.
is this normal? or is there a way to stop these two dialogue boxes from
appearing?

TIA

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate
one order line?
I probably am not making sense, but the idea is that an order sub form
can have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price £20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this case
Prices, i.e. £38

many thanks for your patience on this

Responses inline


Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on the
Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of the
OrderDetails subform you have an unbound text box with the Control
Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only
think its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate
in them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))


Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and
it DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field,
but not the calculated control.


To reference the subtotal text box on the main form you need the
control source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the
column I want, i.e. the sub total of the piece price X the quantity
less the discount

If this answers your questions, remember that you need to be precise in
framing your question. You kept referring to Price as a Column, which I
took to mean Field, but in fact it seems to be the name of a calculated
control. Remember, we can't see your database, so must rely on your
description.

 

[Big Al]

Many thanks for all your help BruceM

Glad to hear you got it working.

Google Groups can be a great help in such cases as the error message. I
searched for the error message, and found the following )posted by Joan
Wild when somebody asked the same question):

You can go to the Tools, Macros, Security and set it to low.

FAQ on macro security in Access
http://office.microsoft.com/en-us/assistance/HA011225981033.aspx


If the security item is missing from the menu, just reset your Menu
Bar.

I should say that when I tried to check that setting it wasn't there, and
I couldn't reset the toolbar, but that's as far as I can go with that. I
would suggest a new thread if you have questions on that particular
subject.

Actually, I have just answered my own question.
Many thanks for getting me out of this mess

One final question. I am using Access 2003.
Each time I open a database I get a security warning dialog box pop up
stating "security warning, unsafe expressions not blocked" and asking me
to either block unsafe expressions or not.
when clicking on no I then get another security warning telling me that
the file may not be safe if it contains code that was intended to harm my
computer and then asks me is I want to open the file. I have to click on
open to get the database to load.
is this normal? or is there a way to stop these two dialogue boxes from
appearing?

TIA

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate
one order line?
I probably am not making sense, but the idea is that an order sub form
can have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price
£20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this case
Prices, i.e. £38

many thanks for your patience on this

Responses inline


Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on the
Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of
the OrderDetails subform you have an unbound text box with the
Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into the
Detail section of the subform?

This is a strange problem and this has confused me more. I can only
think its something to do with the expression in the Price box.
I have the following boxes with the following expressions to calculate
in them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source expression
"Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the form)
"Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))


Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]" and
it DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field,
but not the calculated control.


To reference the subtotal text box on the main form you need the
control source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the
column I want, i.e. the sub total of the piece price X the quantity
less the discount

If this answers your questions, remember that you need to be precise in
framing your question. You kept referring to Price as a Column, which
I took to mean Field, but in fact it seems to be the name of a
calculated control. Remember, we can't see your database, so must rely
on your description.

 

[BruceM]

You're very welcome. Good luck with the project.

Many thanks for all your help BruceM

Glad to hear you got it working.

Google Groups can be a great help in such cases as the error message. I
searched for the error message, and found the following )posted by Joan
Wild when somebody asked the same question):

You can go to the Tools, Macros, Security and set it to low.

FAQ on macro security in Access
http://office.microsoft.com/en-us/assistance/HA011225981033.aspx


If the security item is missing from the menu, just reset your Menu
Bar.

I should say that when I tried to check that setting it wasn't there, and
I couldn't reset the toolbar, but that's as far as I can go with that. I
would suggest a new thread if you have questions on that particular
subject.

Actually, I have just answered my own question.
Many thanks for getting me out of this mess

One final question. I am using Access 2003.
Each time I open a database I get a security warning dialog box pop up
stating "security warning, unsafe expressions not blocked" and asking me
to either block unsafe expressions or not.
when clicking on no I then get another security warning telling me that
the file may not be safe if it contains code that was intended to harm
my computer and then asks me is I want to open the file. I have to click
on open to get the database to load.
is this normal? or is there a way to stop these two dialogue boxes from
appearing?

TIA

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate
one order line?
I probably am not making sense, but the idea is that an order sub form
can have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price
£20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this
case Prices, i.e. £38

many thanks for your patience on this

Responses inline


Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on
the Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of
the OrderDetails subform you have an unbound text box with the
Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into
the Detail section of the subform?

This is a strange problem and this has confused me more. I can only
think its something to do with the expression in the Price box.
I have the following boxes with the following expressions to
calculate in them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source
expression "Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the
form) "Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))


Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]"
and it DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the field,
but not the calculated control.


To reference the subtotal text box on the main form you need the
control source of a text box on the main form set to something like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the
column I want, i.e. the sub total of the piece price X the quantity
less the discount

If this answers your questions, remember that you need to be precise
in framing your question. You kept referring to Price as a Column,
which I took to mean Field, but in fact it seems to be the name of a
calculated control. Remember, we can't see your database, so must
rely on your description.

 

[Big Al]

Thanks,
My company is too cheap to get a proper database manager in, so have left it
up to me! :-(

You're very welcome. Good luck with the project.

Many thanks for all your help BruceM

Glad to hear you got it working.

Google Groups can be a great help in such cases as the error message. I
searched for the error message, and found the following )posted by Joan
Wild when somebody asked the same question):

You can go to the Tools, Macros, Security and set it to low.

FAQ on macro security in Access
http://office.microsoft.com/en-us/assistance/HA011225981033.aspx


If the security item is missing from the menu, just reset your Menu
Bar.

I should say that when I tried to check that setting it wasn't there,
and I couldn't reset the toolbar, but that's as far as I can go with
that. I would suggest a new thread if you have questions on that
particular subject.

Actually, I have just answered my own question.
Many thanks for getting me out of this mess

One final question. I am using Access 2003.
Each time I open a database I get a security warning dialog box pop up
stating "security warning, unsafe expressions not blocked" and asking
me to either block unsafe expressions or not.
when clicking on no I then get another security warning telling me that
the file may not be safe if it contains code that was intended to harm
my computer and then asks me is I want to open the file. I have to
click on open to get the database to load.
is this normal? or is there a way to stop these two dialogue boxes from
appearing?

TIA

Thanks for all your help BruceM.
Just one question.
By putting in to the unbound text box
"=Sum([Quantity]*[UnitPrice]*(1-[Discount]))" will that only calculate
one order line?
I probably am not making sense, but the idea is that an order sub form
can have more than one item in it
i.e. Line 1 product A; quantity 2; Unit price £10; Discount 0% Price
£20
Line 2 Product B; Quantity 1; Unit Price £20 Discount 10% Price £18

And I want to appear in the unbound box, the sum of the two in this
case Prices, i.e. £38

many thanks for your patience on this

Responses inline


Let me see if I understand. You have an Orders table and an
OrderDetails table related one-to-many. You have a form based on
the Orders table, with an embedded subform based on OrderDetails.
OrderDetails contains a field called Price. In the form footer of
the OrderDetails subform you have an unbound text box with the
Control Source set to the expression:
=Nz(Sum([Price]))
but that text box is empty no matter what. Is that correct?

yes that is spot on.

This should work, so something is not as I imagine it from your
description. Read my questions carefully, and be sure you know the
answer to each one.
What happens if you put the text box with the Sum expression into
the Detail section of the subform?

This is a strange problem and this has confused me more. I can only
think its something to do with the expression in the Price box.
I have the following boxes with the following expressions to
calculate in them:

Box 1 "Product ID" No format, control source expression "Productid"
Box 2 "Quantity" Format "general number" and Control source
expression "Quantity"
Box 3 "UnitPrice" Format "Currency" and control source expression
"UnitPrice"
Box 4 "Discount" Format "Percentage" and control source expression
"[Discount]"
Box 5 (The one I am trying to carry over to the main part of the
form) "Price" Format "Currency" and Control source expression
"=[Quantity]*[UnitPrice]*(1-[Discount])

You cannot sum a calculated control, but you can sum the calculation
itself. It sounds as if Price is the name of the control in which the
calculation is contained, in which case the unbound text box in the
subform footer needs to be:

=Sum([Quantity]*[UnitPrice]*(1-[Discount]))


Box 6 (in the form footer) "OrderDetailsTotal" Format "Currency" and
Control Source Expression "=Nz(Sum([Price]))

and this box does not show any figure.
Now I tried changing in the expression "[Price]" for "[UnitPrice]"
and it DID show the sum of the units price columns totalled!

UnitPrice is a field. Price is a calculation. You can Sum the
field, but not the calculated control.


To reference the subtotal text box on the main form you need the
control source of a text box on the main form set to something
like:
=[Forms]![frmOrders]![fsubOrderDetails].Form![txtSubTotal]
(using your actual form and field names, of course).

I have in that text box control source the following expression:-
"=[Order Details Subform].Form!OrderDetailsTotal"
and when using "UnitPrice column" it does work, but not using the
column I want, i.e. the sub total of the piece price X the quantity
less the discount

If this answers your questions, remember that you need to be precise
in framing your question. You kept referring to Price as a Column,
which I took to mean Field, but in fact it seems to be the name of a
calculated control. Remember, we can't see your database, so must
rely on your description.