Writing a value calculated in a form back to a table

[chrisdixon2]

Hello people.

Needing some help with a problem. Im using Access 2007 and having a problem with a sales form. For some background ive just set up a business on my own and wish to make my own sales/inventory/reports system.

My actual problem is on my main Sales form. It is a form with subform setup (a sales and sales line), I want to write the calculated Total field (which is calculated within the subform) and then copy that into a table (an OrderTotal field on my main Sales table). I need it for sales analysis reports.

Thanks

Chris

 

[Simon_MT]

On your subform you can set the Order Total field when you have calculated the Sales Line or once its written away. Alternatively, write a query to update Sales Table from the Sales Lines relating to that Sale.

Simon

 

[Krava]

HI, Chris!

There are a few methods possible.
E.g.:
1) If your main form's record source is the table you want to write the calculated value into, it is quite simple: On some CommandButton (named e.g. "Save Totals") click you can write code me!SubformName![FieldName]=[FielNameWithCalculatedValue], so this is the simpliest way.
2) To use some UpdateQuery that will update your desired record in the table to value from your calculated field.
3) To use the recordset opened on the table and its updating the desired field into value from calculated field.

Krava

 

[chrisdixon2]

Hi, Ive tried that code unfortunately i get "cannot assign a value to this field". Ive attempted to do a query but unfortunately ive never done an update query so not any luck there. Both the form and subform are based on queries. Does this matter?

 

[Krava]

Hi, Chris!
If the both forms are based on query it should not be a problem. I am only wondering about the syntax I have given to you and the error message you got. Could you try to use this: SubformName![FieldName]=[FieldNameWithCalculatedValue]?

Krava

 

[chrisdixon2]

HI again. Yes thats what i thought, from what ive gathered (as im teaching myself) it should be easier. But sorry still no joy same message "cannot assign a value to this field". Any ideas?

Thanks for your help

Chris

 

[chrisdixon2]

Haha done it! Reversed the forms (put the button on the main form) and reversed the code. all working fine now! Thanks!