updating fields automatically

[Scotter]

Hi everyone, I'm having an issue with getting my fields to update when i change records. I have a main form and a subform. The main form has "total", "deposit", and "due" text boxes. The subform has the items, and the item total(# of items*price).

I currently have an update query that calculates the Item total. And in the form I have an OnUpdate event that takes the (Total price for the order) - (any deposit) = to ammount due.

This all works great, accept for the fact if you go to a record with a different order number the total, deposit, and due fields remain the same as they were with the previous record.

From what I understand its not good to store calculated fields within tables so I'm trying to do this within the form.

How can I get the fields to update automatically when i change records?

Thanks,
Scotter

[Rabbit]

Set the control source to the calculation instead of doing it through events and code.

[Scotter]

Well yeah, I guess that would make sence :)

Thanks,
Scotter

[Rabbit]

It's not as obvious as it sounds. Most people don't understand what control source means. Good luck.

[Scotter]

Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.

Here is my "Update to" line in my update query

nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+ nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])

How can I change that into a control source for my text box?
Scotter

[Rabbit]

Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.

Here is my "Update to" line in my update query

nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])

How can I change that into a control source for my text box?
Scotter

You can't reference tables using that method. When you have to get information from a table outside your form's record source, use DLookup() instead.

[Scotter]

Thanks. But now I have another part of the question. On the subform I want to do the same thing, instead of having an update query do the math I want the form to. But I cant get that to work. I keep getting #ERROR.

Here is my "Update to" line in my update query

nz([items].[# of Chairs]*[Price_Chairs].[Price],0)+nz([Items].[# of Tables]*[Price_Tables].[Price],0)+nz([Price_Tents].[Price])+nz([Price_Combo].[Price])+nz([# of Table Cloths]*[Price_Table Cloth].[Price])

How can I change that into a control source for my text box?
Scotter

Ok I figured that out, and it was pretty much what i had there but with an = sign.. Butttt since I'm not actually saving the totals, I cant add them from my main form, I'm assuming its because a subform is an object or something like that. Here is my control source for my Total Price on the main form

=DSum("ItemCost","TestQuery2_subform","[Order Number] = " & [Order Number])

any ideas about how to get thoose values to my mainform without saving them?

[Rabbit]

Ok I figured that out, and it was pretty much what i had there but with an = sign.. Butttt since I'm not actually saving the totals, I cant add them from my main form, I'm assuming its because a subform is an object or something like that. Here is my control source for my Total Price on the main form

=DSum("ItemCost","TestQuery2_subform","[Order Number] = " & [Order Number])

any ideas about how to get thoose values to my mainform without saving them?

I'm not sure what you mean. What values are you trying to get to the main form?

[Scotter]

The values from my subform, the subform is made up of all the items within an order. I cant add the individual prices of each item to get my total. I'm trying to put a total in the footer, but I keep getting #ERROR. Heres what I have in there now

=DSum("Item total","testQuery2 subform","[Order Number] = "& [Order Numer])

[Rabbit]

I still don't follow. Could you provide a layout of your tables, forms, and an example of what you're looking to for?

Aside from that, to reference something on a subform you use Forms!FormName! SubformName.For m.ControlName

[Scotter]

Ok, I figured out a way for everything to work. Insted of calculating the individual item cost of each item within the form, I changed it so the item cost was calculated within the query the form was based off of.

Then in the form I set the item cost field's control source to the ItemCost field in the query. Also in the footer of the form I set a textboxe's control source to
DSum("ItemCost" ,"QueryName" ,"[Order Number] = " & [Order Number])
So I could get the total cost. Then on my main form I changed my "Total" textbox's control source to the textbox in the footer of my subform.

The subform is in datasheet view so you don't see the header and footer, but this way I could still pull from it. Honestly I don't know if this is the best way to do it, but it works for me so I'm happy

Thanks again Rabbit

[Rabbit]

Not a problem, good luck.