Solved Order Totals on a New Order (1 Viewer)

[debsamguru]

Hi, Access newbie here,

I have two tables, OrderHeaderT and OrderDetailsT. I have successfully created a New Order Form, bringing in the header details and a continuous details subform. I have summed the totals on the subform and I can display the totals on the header part of the form using =[New Order Details].[Form]![OrderCostTotal] but it won't update the TotalCostPrice field on the OrderHeaderT. I believe it's because once the cursor moves to the subform, the record is written to the OrderHeaderT, so there's nothing to force the field to be updated. Is my understanding correct? I don't have a button 'Save Order', because it is automatically created as the items are added.

What am I missing?

 

[June7]

Really should not save this calculated value into header record. It should be calculated when needed.
Calculated values do not automatically save anywhere. Saving would require code (macro or VBA).

 

[debsamguru]

The reason I am saving them in the header record is because when I display a list of the orders, I want to be able to change details e.g. delivery date, paid date, status, on that line in the list and not have to go into the whole order. However, once I use a query with multiple tables to display the list of orders, the fields stop being updateable.

 

[June7]

Sorry, don't really understand that described scenario and doesn't change my comment.

If you really want to save calculated value, need code. Code to save is simple. Real trick is figuring out what event to use and might need to be in more than one location, such as buttons on main form to close form and move to new record row.

Me!TotalCostPrice = Me.tbxTotal

Why not use the same form/subform to edit existing data as for entering new?

 

[debsamguru]

I have the following queries:-
OrderDetailsExtendedQ which has all of the OrderHeaderT and OrderDetailsT fields on it
OrderTotalsQ which takes the OrderDetailsExtendedQ and sums the totals to give totals per order
OrderHeaderQ which takes the OrderHeaderT, CustomerT (for the Customer Name)
The OrderF form which lists the orders uses OrderHeaderQ. Using this, the fields are updateable.
However, when I change the query OrderHeaderQ to also take fields from OrderTotalsQ, the OrderF form says 'Recordset is not updateable'.
The only difference is that I have added the OrderTotalsQ into the OrderHeaderQ.

 

[June7]

That is to be expected when aggregate query is involved.

 

[debsamguru]

But how do I get round that?

 

[Minty]

I don't understand why when editing some of the header detail would need the order details total anyway, you can't edit that it wouldn't make any sense to.

You could left join it to a summation sub query to display it and your recordset should still be updateable.

 

[debsamguru]

So my form looks like this:

I want to be able to amend the PO Numbers, dates and status whilst still showing the totals.

What do you mean by a left join - as soon as I put a query in, it changes to recordset unupdateable?

 

[June7]

Could use DSum() domain aggregate function instead of aggregate query. However, domain aggregate can cause slow performance with large dataset.

I agree that showing the aggregate calc seems of little use in this edit.

 

[debsamguru]

Thanks. It is to give them all the important information in a quick view.

 

[Pat Hartman]

To show the detail total on the main form -
1. remove the joins to the totals queries. that is what is making the form not updateable.
2. add several controls to the footer of the subform.
3. make the control's ControlSource's -- =Sum(thefieldyouwanttosum)
4. add controls to the mainform to pull in the values from the subform.
5. make the control's ControlSource's --- =sfrmname!thetotalyouwanttosee

Make sure to use rational control names. Do not let the default name stand.

 

[June7]

@Pathartman, OP is doing that on a form/subform arrangement for entering new data. I already suggested using the same form/subform for editing existing records. They don't want that.

 

[debsamguru]

I haven't got a problem using the form/subform when adding new or editing orders. My problem is with the summary list of orders. As you can see from the picture above, I want to show a list of orders WITH the totals shown. That is why I added the totals fields to the header record - just to be able to show it on this summary list. I'd love to be able to calculate it each time. If anyone can show me how to display the calculated totals on the summary list, then I won't need to add it to the header record. In the absence of that, I need to know how to add the totals to the header field once the order lines have been added to the order.

 

[June7]

Your options are already outlined:

1. use code to save calculated value (see post #4)

2. don't save and calculate when needed with aggregate query Sum() or domain aggregate DSum()

 

[debsamguru]

Thanks all. I'm going to go away and think about it and come back fresh on Sunday to look at it.

 

[Pat Hartman]

I already suggested using the same form/subform for editing existing records. They don't want that.

Then they must know more than the experts if they refuse to take sound advice.

 

[debsamguru]

Thank you all for your help. I managed to do it, as you all said I would, using DSUM. My only remaining problem is that when I add a new order detail line to my order using the continuous subform and tab to the next line, the first line scrolls upwards so that I am left seeing only the order line I am entering. I can see the previous lines if I scroll, but I would like the scrolling not to happen so that I can see all the new lines on the subform. I've obviously set one of the subform formatting fields incorrectly but I don't know which one.

Anyway, thank you for all your help.

 

[June7]

Should only scroll out of sight if screen space is not large enough for form size and/or too many records to display.

 

[debsamguru]

I know, but it does. Here is a picture of my form - as you can see, plenty of room.

As soon as I tab or enter to get to the next row

with the first line going up above the new line for entry.
This is my subform in Design. It's a continuous form so I'm very confused!