How do I write back a calculated value to a table in Access?

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

Guest

I am trying to figure out how to write back a calculated flag value (-1 or 0
based on the values of other fields on the form) to a table in Access without
much luck. Within the form, I have successfully created a text box that is
indicating a -1 or 0. What I can't do is get the -1 or 0 to populate within
the table... what am I doing wrong?
 
Steve,

This issue comes up quite often. The answer is that calculated fields
should be calculated on the fly in a query, using the same expression as your
textbox, rather than stored, because:

- Should one of the fields be changed outside the context of your form that
is driving the calculation, the stored value will become incorrect
- It is much faster to calculate the field on-the-fly than read it from disk

Sprinks
 
I am trying to figure out how to write back a calculated flag value (-1 or 0
based on the values of other fields on the form) to a table in Access without
much luck. Within the form, I have successfully created a text box that is
indicating a -1 or 0. What I can't do is get the -1 or 0 to populate within
the table... what am I doing wrong?
Click to expand...

You're using Access, not Excel.
Why would you want to write that value to a table?

Any time you need the result of a calculation, calculate it, in a
form, on a report, or in a query, just as you are doing it now.
 
The reason why I want to calculate it and write it back is because it is
looking at over 30 fields to determine if an auditor has reported an error
for any of the 30 audit points (building a quality database). The queries
required for all the reports will not work unless there is a value there
because the criteria statements are just too complex looking at every
combination of the 30 fields... it may not be the "best" way, but can it be
done?
 
The reason why I want to calculate it and write it back is because it is
looking at over 30 fields to determine if an auditor has reported an error
for any of the 30 audit points (building a quality database). The queries
required for all the reports will not work unless there is a value there
because the criteria statements are just too complex looking at every
combination of the 30 fields... it may not be the "best" way, but can it be
done?
 
Steve,

The only way to save the value is to write code to do it. If you want the
value "live", i.e., accurate based on the current values of all of the 30
fields, you'd assign this code to the AfterUpdate event of each of these
fields, which would write the value of the calculation to a control bound to
your boolean field.

Dim blnValue as Boolean
blnValue = <your calculation here>
Me![YourBoundControl] = blnValue

You can see why Fred and I don't understand why you'd want to do this--30
AfterUpdate event procedures rather than a calculated query field that would
always be accurate.

If the calculation is complex, perhaps a better solution is a Public
Function that takes the 30 field values as parameters and returns a boolean
result. Then the calculated query field call would be:

= MyBooleanFunction(fld1, fld2, ....fld30)

If none of this seems to fit your situation, please provide detail on how
you're doing this calculation, and we'll see if we can help.

Sprinks
 
Back
Top