Solved Connecting sums from order detail to order list

[asteropi]

My database is in Greek so I'm suppying the picture below with explanations.

I have an Order Detail Form and a Order List form. It just so happens that my returning wholesale customes sometimes call me and I need to have that total sum listed in their list.
However, the total sum is an unbound textbox that generates the total within the form and I'm trying to transfer it to my List Form/Table but I can't. How do I typically go about that?
I thought maybe insert_to-table command in the after_update box? but I don't know how to do that.
Or for the list_form field to grab the sum with a =Forms!(DetailForm)!(TotalSum) command. I tried that and failed miserably

 

[Pat Hartman]

There is no reason to save the sum. In the subform, add an unbound control to the footer section. Name it txtSum. In it's ControlSource --
=Sum(AmountField)

In the AfterUpdate event of the subform

Me.Parent!txtSumFromSubform = Me.txtSum

 

[Gasman]

Firstly, that is a subform, so the syntax is different? See a patter here?
Save this link, as you are going to need it.

Forms: Refer to Form and Subform properties and controls

Generally calcuated values are not stored.
Also perhaps before you get in deeper than you already are, review the normalization already mentioned.

Making a query updatable

Hello to everyone. I'm having trouble to make a query updateable, and I'd like some help, if there's a solution for it. I have my main customers table (tbl_Customers) and two tables (tbl_DateA , tbl_DateB) that represent event dates related to the customers. (each table represents completely...

www.accessforums.net

A DSum() would give you what a customer owes, but depending on how your database has been structured.

 

[tvanstiphout]

The Northwind 2 Templates show you how to create an OrderList with the OrderTotal amount in it:

 

[Pat Hartman]

But that query is not updateable.

 

[asteropi]

Firstly, that is a subform, so the syntax is different? See a patter here?
Save this link, as you are going to need it.

Generally calcuated values are not stored.
Also perhaps before you get in deeper than you already are, review the normalization already mentioned.

A DSum() would give you what a customer owes, but depending on how your database has been structured.

I already started reading about the normalization! And that link is very helpful thank you.
I don't know how the DSum() is different from the Sum, but I'll look it up.

To my problem now, I don't need to store it at the table, so that's not a problem. I'll just reference it on the list.
I repost that picture becuase I have 2 main forms and 2 subforms and I don't know which command to use. Please guide me to the proper one.
Which subform is considered 1 and which 2

In the subform, add an unbound control to the footer section. Name it txtSum. In it's ControlSource --
=Sum(AmountField)

I'm guessing you mean the Order detail? I did that. That Total Sum textbox in the Order_Detail_sub is exactly what you describe.

In the AfterUpdate event of the subform

Me.Parent!txtSumFromSubform = Me.txtSum

Can you please see the picture with the forms and subforms names and tell me which parent you meant and which subform.
Because I tried it as below and it didn't work:

In the After_update of the TotalSumtxt of the Order_Detail_Sub I wrote:
Me.CustomerF!OrderListSubformtxt = Me.TotalSum

 

[Pat Hartman]

The two main/sub pairs are not related. One has nothing to do with the other. How would I know which pair you are talking about. A subform is a form that is placed on another form. Assume you have 4 forms A, B, C, D. If you place B on A and D on C then B is a subform of A and D is a subform of C. A/B has NOTHING to do with C/D

 

[asteropi]

The two main/sub pairs are not related. One has nothing to do with the other. How would I know which pair you are talking about. A subform is a form that is placed on another form. Assume you have 4 forms A, B, C, D. If you place B on A and D on C then B is a subform of A and D is a subform of C. A/B has NOTHING to do with C/D

Ok
So I want to refer SubD to SubB. Main forms remain untouched

And since the two forms are unrelated I tried this command on the control source of that text box

=Forms!Mainform!Subform1.Form!ControlName or properly =Forms![OrdersF]![OrderDetailsF]![TotalSum]

But it gives me #Error

 

[Gasman]

You are not using the Form object in your syntax.
Understand the heiarchy

Mainform
 Subform control
   Form
     Control

 

[asteropi]

Mainform
Subform control
Form
Control

=Forms![OrdersF]![OrderDetailsF].Form![TotalSum]
tried it this way as well. Still getting #Error

 

[Gasman]

Upload the dB with enough to see the issue and instruction on how to recreate.
You do.need to use the subform control name NOT the subform name, though if you use wizards, they are one and the same.

 

[LarryE]

=Forms![OrdersF]![OrderDetailsF].Form![TotalSum]
tried it this way as well. Still getting #Error

=Forms![OrdersF]![OrderDetailsF]![TotalSum]

 

[Pat Hartman]

So I want to refer SubD to SubB. Main forms remain untouched

That doesn't make any sense. The two main forms should probably not both be open at the same time. This will only confuse the user. But even worse, the two subforms have nothing to do with each other so why would you be showing data from one on the other? It would be like showing the number of pets a student has on a teacher's class schedule.

 

[asteropi]

Upload the dB with enough to see the issue and instruction on how to recreate.
You do.need to use the subform control name NOT the subform name, though if you use wizards, they are one and the same.

I only ever start from blank templates, but I do have the tendency of naming things the same. Anyway, I recreated it from scratch and put some random numbers.
The only thing I didn't manage to make it work - heck i didn't remember how - was to synch the detail subform in the order form. But in my main db it works fine so I didn't think it was that important.

The sum I wish to fix clearly gives a Name??? error cuz it has no command.

=Forms![OrdersF]![OrderDetailsF]![TotalSum]

tried it. doesn't work

That doesn't make any sense. The two main forms should probably not both be open at the same time. This will only confuse the user. But even worse, the two subforms have nothing to do with each other so why would you be showing data from one on the other? It would be like showing the number of pets a student has on a teacher's class schedule.

It's a step process
Open Customer ->see the subform list -> open main form Order with subform details

 

[Pat Hartman]

1. You need to add relationships and enforce Referential Integrity. You have customers 2,3,4 in the customer table but 1,2,3,4 in the orders table. To fix this, you need to delete the orders for customer 1 from the orders table - OR - I added cust 5 and changed the 1's to 5's in the Orders table.
2. Remove the OrderDetails table from the Recordsource query of the OrdersF form.
3. Remove the order sums from BOTH subforms. Neither will work.
4. on the OrderDetails subform, set the Master/Child links to OrderID so the subform will sync.
5. Unless your products NEVER change their price, you need to save the price in the OrderDetails table when the order is placed. If your business rules say that if the product price decreases before the order ships, the customer gets the lowest price, you have to update each unshipped order. This is way beyond what you have so far so let the update be for now. Just store the price. Otherwise, you won't be able to do any historical reporting since changes to the price will also change the price for old orders.

And lots more.

Your best option is to review the Northwind dev application since it is an Order Entry application and it handles much of what you need your app to handle. If you read/write English, then don't change any of the object names in the database. All you need to change is the labels on the forms.

 

[asteropi]

1. You need to add relationships and enforce Referential Integrity. You have customers 2,3,4 in the customer table but 1,2,3,4 in the orders table. To fix this, you need to delete the orders for customer 1 from the orders table - OR - I added cust 5 and changed the 1's to 5's in the Orders table.
2. Remove the OrderDetails table from the Recordsource query of the OrdersF form.
3. Remove the order sums from BOTH subforms. Neither will work.
4. on the OrderDetails subform, set the Master/Child links to OrderID so the subform will sync.
5. Unless your products NEVER change their price, you need to save the price in the OrderDetails table when the order is placed. If your business rules say that if the product price decreases before the order ships, the customer gets the lowest price, you have to update each unshipped order. This is way beyond what you have so far so let the update be for now. Just store the price. Otherwise, you won't be able to do any historical reporting since changes to the price will also change the price for old orders.

Hey thanks for all that.
My proper db is not such a mess. I did this really quickly.
1. I was typing at random and didn't notice. But thank you for pointing out.
2. Yup, my db doesn't have that
3. This is the only thing that I literally NEED to work
4. Did that, hadn't noticed. In my db it works fine
5. I have customer specific pricing and too many products to remember the details for every order. So in my proper db I have customerPrice table connected to specific product IDs -> those feed to a query of mixing properprice and customer price ->and then feed to OrderDetails form. So there is no way I can do that manually. I simply don't remember all the prices for all the products for all the customers.

Here I only made a sample and input 3 random products with 3 random prices. It doesn't correspond 100% to my database. But it's also irrelevant.
It was just a sample so you can understand the relation I need to create. I'm sorry if my hasty mistakes took you off course but going back to order sums, in my proper db the OrderDetail Form Sum works great. I only need to transfer it to the OrderList Subform and every command I tried gives me #Error or Name?
I updated the sample and re-attached.

 

[Pat Hartman]

3. This is the only thing that I literally NEED to work

But that isn't how you do it. Use a DSum() AND you don't put the total on the subform. You put it on the main form. Someone told you earlier to use a DSum() so I didn't repeat the advice.

Functions that start with D are Domain functions. They run a query to achieve the result. Notice the syntax.

Me.txttotal = DSum("columnname", "QueryORTablename", "CustomerID = " & Me.CustomerID)

Other aggregate functions such as Sum, Min, Avg, etc Aggregate the Recordset that is bound to the current form. You are NOT aggregating data bound to the current form. You are aggregating data from a table. Use a Domain function to do that.

You really need to shift gears and use the Northwinds dev sample. It has much of what you need already built. The problem is the language that is used to display the results. If you can deal with developing in English, you will save a lot of time and effort.

 

[asteropi]

Oh! I just built mine following a tutorial almost step by step and I just tweek it where I need to (like the customer codes pex). But the guy had the total in the subform. Anyway, I'll read on what you mentioned and try to fix it. Thank you

 

[asteropi]

It worked! Thank you very much everybody! It seems I have a lot to learn!