Re: Form calculation is not working

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Many thanks for all your help BruceM

"BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
news:eAYgPBntGHA.3912@xxxxxxxxxxxxxxxxxxxxxxx
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.

"Big Al" <Big al@xxxxxxxxxxxxxxxx> wrote in message
news:VISdnajs67Y7nUzZRVny3w@xxxxxxxxx
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

"Big Al" <Big al@xxxxxxxxxxxxxxxx> wrote in message
news:PMKdnZbQuK-aYE3ZRVnyiw@xxxxxxxxx
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 :-)

"BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
news:ui4iAbmtGHA.4872@xxxxxxxxxxxxxxxxxxxxxxx
Responses inline

"Big Al" <Big al@xxxxxxxxxxxxxxxx> wrote in message
news:bPadnWGIA6zQeU3ZRVny2w@xxxxxxxxx

"BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
news:eKaO%23AatGHA.4748@xxxxxxxxxxxxxxxxxxxxxxx
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.













.

Relevant Pages

  • Re: Form calculation is not working
    ... My company is too cheap to get a proper database manager in, ... OrderDetails contains a field called Price. ... You cannot sum a calculated control, but you can sum the calculation ...
    (microsoft.public.access.forms)
  • Re: Form calculation is not working
    ... And I want to appear in the unbound box, the sum of the two in this case ... OrderDetails contains a field called Price. ... OrderDetails subform you have an unbound text box with the Control ... You cannot sum a calculated control, but you can sum the calculation ...
    (microsoft.public.access.forms)
  • Re: Form calculation is not working
    ... And I want to appear in the unbound box, the sum of the two in this case ... OrderDetails contains a field called Price. ... OrderDetails subform you have an unbound text box with the Control ... You cannot sum a calculated control, but you can sum the calculation ...
    (microsoft.public.access.forms)
  • Re: Form calculation is not working
    ... And I want to appear in the unbound box, the sum of the two in this ... OrderDetails contains a field called Price. ... You cannot sum a calculated control, but you can sum the calculation ...
    (microsoft.public.access.forms)
  • Re: #Error on Sum Formula from Subform
    ... You didn't mention how you are going about getting the sum of this calculated control onto your main form. ... But I think you will need to do the sum in a control in the Header or Footer of the subform, and then reference *that* control on the main form. ...
    (microsoft.public.access.forms)