Moving a total from a subform to the table of the main form

[Steve in MN]

I have some calculations in the form footer of the sub form that transform
yes/ no answers into point values. The end result gives a percent score and
before the record set moves to a new record, i want to be able to move the
calculated text [score] box value to a field in the main form table.
I know how to set values using if this, then Me![score]=[score]
But was wondering how to do it across 2 forms.
the unbound text box in the sform sfrmSdata is called "score" and the field
in the main form frmSurvey is also called "score"
Or what other methods are available to do this?
I'm only self taught, so forgive me if some of my methods are not orthodox.
Thanks

 

[Arvin Meyer [MVP]]

Calculations need to stay in the forms or reports, and not be saved to the
tables that contain the other data. Since they can be recreated in an
instant, you have no legitimate reason to break Normalization rules to store
them.

 

[tina]

before the record set moves to a new record

well, it's not clear whether you mean move to a new record in the subform,
or move to a new record in the main form. if it's the subform, you can run
code in the form's AfterUpdate event procedure to set the value of the field
in the mainform's RecordSource, as

With Me.Parent
!FieldName = Me!TextboxName
.Dirty = False
End With

if the record is in the mainform, hmmm, i'd probably try using the form's
Exit event first. if you run into problems, start reading up on the
available form events to see what else might serve the purpose - or post to
the ngs again, probably you'll get better advice!

having said all that, i have to also say that storing calculated values is a
violation of normalization rules - and just a generally bad idea. unless you
have a sound business reason for storing the calculated value, i'd recommend
that you don't - as long as the raw data is stored in the db, you can
calculate it on the fly whenever you need it, and it will always be runtime
accurate.

hth

 

[Arvin Meyer [MVP]]

Once again:

Calculations need to stay in the forms or reports, and not be saved to the
tables that contain the other data. Since they can be recreated in an
instant, you have no legitimate reason to break Normalization rules to store
them.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Yes, it is taking a Transformed value in the sub form footer and i wanted
to
store it in the main form table.
I found some other posts relating to getting the information into a text
box
up on the main form using;
=IIf(IsError(sfrmSdata.Form![%score]),0,sfrmSdata.Form![%score])
In the text box's control source, however it doesn't seem to update to the
table, just displays in the main form text box as the value.
This would be all i need to see the score on the form and it doesn't have
to
save it, i can recreate (as described above) the transformed values on the
reports.

before the record set moves to a new record

well, it's not clear whether you mean move to a new record in the
subform,
or move to a new record in the main form. if it's the subform, you can
run
code in the form's AfterUpdate event procedure to set the value of the
field
in the mainform's RecordSource, as

With Me.Parent
!FieldName = Me!TextboxName
.Dirty = False
End With

if the record is in the mainform, hmmm, i'd probably try using the form's
Exit event first. if you run into problems, start reading up on the
available form events to see what else might serve the purpose - or post
to
the ngs again, probably you'll get better advice!

having said all that, i have to also say that storing calculated values
is a
violation of normalization rules - and just a generally bad idea. unless
you
have a sound business reason for storing the calculated value, i'd
recommend
that you don't - as long as the raw data is stored in the db, you can
calculate it on the fly whenever you need it, and it will always be
runtime
accurate.

hth

I have some calculations in the form footer of the sub form that
transform
yes/ no answers into point values. The end result gives a percent
score and
before the record set moves to a new record, i want to be able to move
the
calculated text [score] box value to a field in the main form table.
I know how to set values using if this, then Me![score]=[score]
But was wondering how to do it across 2 forms.
the unbound text box in the sform sfrmSdata is called "score" and the field
in the main form frmSurvey is also called "score"
Or what other methods are available to do this?
I'm only self taught, so forgive me if some of my methods are not orthodox.
Thanks