How do I store a value from a form into a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On my form I have a field that adds then displays the values of two previous
fields however the table is not updating this value. What should I do to
store the value in the table? I have the following syntax.
Sub AfterUpdate CalcTotPrice()
If IsNull(Me.FPrepaid) Or IsNull(Me.APrepaid) Then
Me.Prepaid = FPrepaid + APrepaid
End IF
End Sub
 
On my form I have a field that adds then displays the values of two previous
fields however the table is not updating this value. What should I do to
store the value in the table? I have the following syntax.
Sub AfterUpdate CalcTotPrice()
If IsNull(Me.FPrepaid) Or IsNull(Me.APrepaid) Then
Me.Prepaid = FPrepaid + APrepaid
End IF
End Sub
Click to expand...

You shouldn't store that value.
You already have stored FPrepaid and APrepaid.
Any time you need to know the value of [Prepaid], re-compute it.
Storing derived (calculated) values goes against the rules of
relational database design.
 
You shouldn't store that value.
You already have stored FPrepaid and APrepaid.
Any time you need to know the value of [Prepaid], re-compute it.
Storing derived (calculated) values goes against the rules of
relational database design.
Click to expand...
Right. But what if someone has thousands of records in a table? Do you
suggest to recalculate them each time when openning the base?
I have a similar question though. I got to add to each record in the table
two additional fields whose values are to be calculated on the base of the
other fields
from the same record (it is sort of a scoring procedure for the data). The
calculation procedure is very complicated. The question is what is the best
way of doing that? I don't want my operators to click on a button created
specially to run the scoring procedure. What I would like to do is to
calculate the values of these two fields as soon as operator finishes data
entry in a record via interface form.
 
You shouldn't store that value.
You already have stored FPrepaid and APrepaid.
Any time you need to know the value of [Prepaid], re-compute it.
Storing derived (calculated) values goes against the rules of
relational database design.
Click to expand...
Right. But what if someone has thousands of records in a table? Do you
suggest to recalculate them each time when openning the base?
I have a similar question though. I got to add to each record in the table
two additional fields whose values are to be calculated on the base of the
other fields
from the same record (it is sort of a scoring procedure for the data). The
calculation procedure is very complicated. The question is what is the best
way of doing that? I don't want my operators to click on a button created
specially to run the scoring procedure. What I would like to do is to
calculate the values of these two fields as soon as operator finishes data
entry in a record via interface form.
Click to expand...

It doesn't matter how many records you have. The answer is the same.
On the form used for data entry or data display, add an unbound
control. Set it's control source to something like:

=([FieldA] +[FieldB]) * .5 (or whatever your calculation should be).
This calculation gets done for each record as you navigate to that
record, not in advance. In a report, the calculation is done as it
comes up for printing/display.
 
You shouldn't store that value.
You already have stored FPrepaid and APrepaid.
Any time you need to know the value of [Prepaid], re-compute it.
Storing derived (calculated) values goes against the rules of
relational database design.
Click to expand...
Right. But what if someone has thousands of records in a table? Do you
suggest to recalculate them each time when openning the base?
Click to expand...

No. You recalculate each one individually as it's needed for display
on a Form or a Report. That's MUCH faster than a disk fetch.
I have a similar question though. I got to add to each record in the table
two additional fields whose values are to be calculated on the base of the
other fields
from the same record (it is sort of a scoring procedure for the data). The
calculation procedure is very complicated. The question is what is the best
way of doing that? I don't want my operators to click on a button created
specially to run the scoring procedure. What I would like to do is to
calculate the values of these two fields as soon as operator finishes data
entry in a record via interface form.
Click to expand...

If you can DEMONSTRATE that a fast, modern CPU cannot calculate the
value fast enough for the user, then you can in fact store the value
into the table. It's a very bad idea, generally - once you have the
value stored, *it* can be edited, or any of the fields underlying the
value could be edited; you would then have a value in your table that
is *wrong*, with no easy way to detect that fact.

If you insist on doing so, though, knowing the danger: use the Form's
BeforeUpdate event. You'ld have one textbox displaying the calculated
expression (say from a VBA function), let's call it txtCalc; and
another textbox bound to the table field, txtStore. In the Form's
BeforeUpdate event put code like

If Not IsNull(Me!txtCalc) Then
Me!txtStore = Me!txtCalc
End If


John W. Vinson[MVP]
 
Back
Top