Update query with calculated fields?

[shaunacol]

I have a form that works well, it allows a member of staff to enter multiple attractions, the type of ticket for that attraction and it pulls in the price then they can add the quantity and it automatically adds up how much the customer owes in the field TotalPrice (all these fields are in the table Tbl_OrderDetails). Since they can order multiple attractions I need to somehow capture the total amount of the order in euros (alongside the order date, time, staff member, amount of deposit required and balance). All these details are fields in the tbl_Order. I am struggling with calculated fields. I need the sum of the TotalPrice field to commit into Tbl_OrderDetails TotalOrderPrice . I have tried Sum([TotalPrice]) in the footer of the form but get #error. I thought it might work if I used an update query and the user has to press a button to run it. Then it could automatically commit to tbl_Order but I cant get the syntax for the sum calculation to work in there either. Is it possible to use calculations like this in a update query or should I be thinking of other methods? The order table does appear as a subform in the form and I have also tried setting the default value to sum the TotalPrice but I get #name. I am a bit stuck now....Thanks in anticipation for any help....

[June7]

Usually, saving aggregate data is not advised. Aggregate data should be calculated when needed. TotalOrderPrice and Balance can be calculated when needed.

An UPDATE query should not be needed. Is this a form/subform arrangement. Is main form bound to tbl_Order? Is subform bound to tbl_OrderDetails?

[shaunacol]

Hi, yes the 2 tables are related one to many and I do have a form/subform arrangement. The problem I have is that TotalPrice is a calculated field (Price*quantity). I have to either save this value or I have to save TotalOrderPrice otherwise if the ticket price for an attraction ever changes I wont know what the customer actually paid in that order if I work it out 'on the fly'. I hope this makes sense.


Also, I am struggling to calculate TotalOrderPrice (my sum calculation in the footer doesn't work) I am wondering if my SUM doesn't work because it s based on a calculated field. (i.e. OrderPrice is quantity *TicketPrice). Perhaps this is why sum wont work?

[June7]

If the field you want to save value into is in the form's RecordSource, all you need in order to save value is:

Me!fieldname = Me.controlname

The real trick is figuring out what event to put this into. This simplest approach is to save the Price and Quantity for each line item then calculate totals.

Save raw data. Calculate totals when needed.

Aggregate functions must reference field - natural or calculated. Cannot reference control.

If you want to provide db for analysis, follow instructions at bottom of my post.

NOTE: I deleted duplicate thread.

[shaunacol]

Thank you for your help. Yes I am really struggling with which event to put this code in. I will have a similar problem if I try to save the raw data as the Price is being pulled from tbl_Price by a Dlookup. So again, I would need a way to save that actual value to a field in tbl_orderDetails. I will try to attach dbase now, that is very kind of you.

[shaunacol]

I have attached dbase. The form is called Frm_OrderClient and has 2 subforms in it. You will see a place to enter attractionID so I have left one record in there which is X002A if you want to test it. Thank you in advance.

[June7]

Why is price pulled by a DLookup? If product is selected by a combobox, why not have the price as a column in the combobox? Then code can reference combobox column by index (index begins with 0), like:

Me!Price = Me.combobox.Column(2)

Put code in the combobox AfterUpdate event.

Looking at db now. Which form is involved - Frm_OrderAttraction? This form errors because the subform name is misspelled in the subform container control SourceObject property.

Okay, now seeing the complication with selecting price. It is dependent on the selected attraction and ticket type. This means if either is changed, the price changes. The code could be in AfterUpdate event of both controls. But there seems to be other factors for ticket price - at the door with/without IVA.

Why not a combobox for selecting attraction on main form?

AttractionID control in subform should be Locked Yes and TabStop No or make it not visible. No reason for AttractionName in subform. OrderDetailsID could also be hidden, definitely TabStop No.

[shaunacol]

Thank you for getting back to me so quickly. I don't know how to create a combobox like that. Also, there are hundreds of attractions so wont that be too much for a combo? Sorry please ignore Frm_OrderAttraction, that was a test one. The forms that im working on are Frm_OrderClient which has 2 subforms (Frm_OrderSub and Frm_OrderDetailSub). For tickets price there are several factors but the 'at the door' etc is what the attraction charges. Although we may need this later it's not relevant for this form. All I need is the salesprice as that is what this company sells at, I hope that makes sense.

[June7]

Hundreds is not too many for a combobox, thousands might be.

RowSource could be like: SELECT SKU, AttractionName FROM Tbl_Attraction ORDER BY AttractionName;

Including the price in combobox RowSource would be complicated for your situation.

Set LimitToList to Yes and user can only select a valid SKU.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

My suggestion for saving individual ticket price into record remains same. Code could be behind each data entry control that impacts price or behind the form BeforeUpdate event.

However, to save the extended price (price * quantity), I changed textbox name to TotalPrice (no spaces), then in code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!TotalLinePrice = Me.TotalPrice
End Sub

For FinalPrice, change expression in textbox to: =[TotalPrice]-[TotalPrice]*Nz([Discount],0)
Then change the event code if you want to save FinalPrice.

[shaunacol]

Many thanks for all your useful advice, it is working ok now, not perfect but well enough! thanks again