If you were me - calculated fields write back to table

[IRedsell]

I have a number of calculated fields in a subform that are coded into controls for the sake of speed and because they are always recalculated when their parents change. I want to write these back into the table in unbound fields - why because I want to track what data was used at the time of calculation in case the user changes their mind! I also want to use these pieces of data for reports and I don't want to programme reports with lots of concurrent data back to the form.

Here is my question.

If I use a calculated field on the form I cannot bind it and so I have to have a way of reading the fields record by record and then writing back to the database the final answers.

I have enough knowledge to use a recordset to read in the record from the data table and can change the first item in the form, however I think what I need to do is

Load the recordset
while not EOF
Read the ID of the first record in the form
Search the recordset for the ID
Update the recordset with the calculated fields using me!field
Move to the next record in the table (How do I do this)
Search again

Is this right and how do I point focus back to the subform record if the process is initiated from a button outside?

Or am I doing this the hard way is there a simpler method
Thanks - BTW I have read through the cookbook already and missed this issue

 

[SeeThru]

Cople of things:

Open the recordset with a WHERE to restrict it to just the records you need. It will speed things up as it will be a smaller recordsety and no search required if you are only doing a few or one record.


Other than that, you need a loop:

Do while rst.EOF = false
rst.edit
(your calculations bit)
rst.update
rst.movenext
Loop

rst.close



The otehr option is to get the fiels to recalculate on the form whenver updates are made to the subfom, writing the information straight into the main table - no recordset required as you can have the fields eitehr visible or hidden on the main form.

 

[IRedsell]

SeeThru

Thats great information, my challenge with the additional fields is caused by me electing to use a datasheet and so all the redundant fields are at the end and as far as I can see they cannot be made invisible.

The rst.edit bit is the bit I can't see the wood for the trees over though

If I am in a subform I am looking at one record of the form at a time, if I use the rst to move forward do I have to repaint the form to recalculate the Me! fields? Or is there some other way of indexing through the calculated fields?

I can't help feeling I am making this too complicated?

 

[IRedsell]

Just for the sake of a white knight riding over the hill here is my code

[proposal subform] sits inside newproposal_form, boy I wish I had put that underscore in at the definition stage!!!

Here is the code
' Select the fields from the form to write out to the database for the component_number selected
While rs.EOF = False
' Need to walk the table
DoCmd.GoToRecord acDataForm, "([proposal subform])", acNext
If Me!txtID = rs!ID Then
'If Me!txtID = rs!ID Then
'Update this one
rs!Extended_Cost = Me![CalcExtended_Cost]
rs!Accessory = Me![CalcAccessory_Cost]
rs!Finance_Rate = Me![Finance_Rate]
rs!MonoCost = Me![CalcMonoCost]
rs!ColourCost = Me!CalcColourCost
rs!Financed_Cost_Qtr = Me!Financed_Cost_Qtr
rs!CPPinclusive = Me!CPPinclusive
rs!CPPExclusive = Me!CPPExclusive
.Update
End If
rs.MoveNext
Wend

 

[SeeThru]

Hiya..

Couple of things....

If the fields are all available on the subform, do the calculation to the filds on the form, not in a recordset - saves opening and closing one.

In your exaple aboe, you are opening a recordset, and then looking for a record. It's much faster to open a smaller recordset with only the record you want ie

dim strSQL as string

strSQL="SELECT * FROM mytable WHERE ID='" & me!txtID & "'"
docmd.openrecordset(strsql)

You should only get the one record, or a group of all records with that ID. you still need to test for EOF to make sure a record exists.

Since you are making changes in the background, you will probably need to issue the me.refresh or me.requery to show the updated data.


If all the fields that you want to change are visible, dont bother with opening a recordset at all. Just refer to the fields on your calculation...

parent.forms!extendedcost=me!calcextended_cost
parent.forms!accessory=me!calcaccesory

of course, you will have to make sure you refer to the proper names of the controls.