Saving a Calculated field from Form to Table

[indygolf]

I'm creating a customer orders database. In the subform I have a running subtotal in form footer as suggested. I then have that amount shown on the main form using =[Inventory Customer Order Subform].form![Order Subtotal]. However, I can't seem to get that amount to populate in my orders table. I want to use this amount to help create an End of Month Statement. Any help would be appreciated.

IndyGolf

 

[PaulBricker]

Add a textbox with the Control Source set to field in the Table you want to save the value to. Then in an event, like the Lost Focus event for your Subform, put
Me.TextboxName = [Inventory Customer Order Subfrom].Form![Order Subtotal]

This will return the value to your table.

Paul

 

[ZmrAbdulla]

Hi,
It is not suggested to save calculated result in table. If it is must then you can use

'==================0000000=================

Private Sub Amount_Enter() 'This is your mainform field
Amount = Inventory Customer Order Subform!Order Subtotal
End Sub
'==================0000000=================

You can also use Afterupdate Event of any other field to populate this.
Regards

Zameer Abdulla

http://www.mtkhoory.com

http://www.aikah.com

 

[Nowell]

Hi,

Are you linking the text box on your main form to the field in your table?

Nowell

 

[indygolf]

I understand I would rather not save a calculated field on a table. I wanted to be able to create a monthly statement where there would be one data line with an order total vs. listing all the detail items with it. This was the best alternative I could think of.

As far as linking, I have only the formula siting in this textbox field. I was trying to see if there was a way to link this back to the table so that it would be a "bound" field, but have not figured it out.

Thanks to all for your replies.

IndyGolf

 

[Nowell]

Hi,

<I understand I would rather not save a calculated field on a table>

Where else do you want to save it? its a database


Do you have a field in your table to hold this value?

 

[indygolf]

Yes I do. In a Customer Orders Table I save the following:

Customer Order ID
Customer Order Date
Customer ID
Quote/Order
Order Total

The first four fields populate fine. Its just getting the total to fill in.

Thanks, IndyGolf

 

[Nowell]

Hi,

You might want to add a 'acSaveRecord' command to your form, on a close event or something similar, that should force it to save the total.

 

[indygolf]

Thanks to all that replied. I will try each to see if they will accomplish my task

IndyGolf

 

[PaulBricker]

Just for some clarification here, the reason it does not save to the table is because you have the Control Source set to the subform textbox. It's referred to as a calculated control and is standard stuff in Access. Using the acSaveRecord command will not help at all. The only way to get your value from the subform into the table is to force the value using code in an event procedure. You set the control source for the Control on the Form to the field in the table you want to store the value in, and then you set the value of the form control to the value in the subform using code. It's not hard and in this case I would say it's a reasonable thing to do.

Paul