Storing calculated field

  • Thread starter Thread starter bbrazeau via AccessMonster.com
  • Start date Start date
B

bbrazeau via AccessMonster.com

I have read many opinionated threads in these groups concerning calculated
fields, but haven't found one that addresses this. Is there a simple way to
have an unbound field on a form continuously update a value derived from
caclulations performed thru VB code on fields in that record? I know I know I
can simply write an expression in the controls window but the formula can be
lengthy and involved and would be much nicer to work with if I can leave it
as VB code. And I would like it to continuously update so the user can change
vaues until they get an acceptable answer.any suggestions?
 
If these are calculations that are being done after specific events, you
need to update the field during your calculations (use DoEvents and/or
Me.Refresh to ensure that the new value shows on the form). Alternatively,
you can use the form's Timer event to redo the calculation every n seconds.
 
You could create a public procedure to calculate the value, and call that
procedure from the After Update event of all controls containing values that
are involved in the calculation.
The advice against storing a calculation concerns storing a calculated
result in a table field. The means by which the calculation is produced
needs to be saved, whether in a control's record source (what you refer to
as the control's window, I think), in VBA, in a query, or what have you.
 
Hi Douglas, The timer event suggestion seems like what I was looking for, as
the other options require coding in each control that could affect the
calculation, before I try it could you or any one else tell me if there are
any draw backs to this and what would be a reasonable time say every 2
seconds?.
If these are calculations that are being done after specific events, you
need to update the field during your calculations (use DoEvents and/or
Me.Refresh to ensure that the new value shows on the form). Alternatively,
you can use the form's Timer event to redo the calculation every n seconds.
I have read many opinionated threads in these groups concerning calculated
fields, but haven't found one that addresses this. Is there a simple way to
Click to expand...
[quoted text clipped - 4 lines]
as VB code. And I would like it to continuously update so the user can change
vaues until they get an acceptable answer.any suggestions?
Click to expand...
Click to expand...
 
The major drawback is that having a timer on a form does weird things while
you're developing.

Other than that, there shouldn't be much of an issue with it: the timer
tends to use virtually no resources.

As to how frequently you need to recalculate, I'm afraid only you can decide
that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


bbrazeau via AccessMonster.com said:
Hi Douglas, The timer event suggestion seems like what I was looking for,
as
the other options require coding in each control that could affect the
calculation, before I try it could you or any one else tell me if there
are
any draw backs to this and what would be a reasonable time say every 2
seconds?.
If these are calculations that are being done after specific events, you
need to update the field during your calculations (use DoEvents and/or
Me.Refresh to ensure that the new value shows on the form). Alternatively,
you can use the form's Timer event to redo the calculation every n
seconds.
I have read many opinionated threads in these groups concerning
calculated
fields, but haven't found one that addresses this. Is there a simple way
to
Click to expand...
[quoted text clipped - 4 lines]
as VB code. And I would like it to continuously update so the user can
change
vaues until they get an acceptable answer.any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas,
I have the calculated field working. I set the timer for 5000 which
translates to every 5 seconds. Now , I decided to test what I've done so far
so I went to make a report and found that I can't get the report to have the
calculated field on it. The auto report wizard will only allow you to select
tables or queries. How can I get this calculated control to show up on a
report?If I make a query and put the calculated field in it will it show up
on a report? Would it show up in a form based on that query?
 
The general advice is not to store calculated fields. As fellow Access MVP
John Vinson likes to say "Storing calculated data generally accomplishes
only three things: it wastes disk space, it wastes time (a disk fetch is
much slower than almost any reasonable calculation), and it risks data
validity, since once it's stored in a table either the Total or one of the
fields that goes into the total may be changed, making the value WRONG."

That means you need to redo the calculations in your report, or else in the
query on which the report is based.
 
There may be some terminology confusion here. In your initial posting your
referred to the calcualtion being performed in an "unbound field". I took
that to mean an unbound control such as a text box. Unbound means it isn't
associated with stored data (or with a calculated query field). An unbound
field is a contradiction in terms.
If your calculation is to sum two number fields, the text box in which you
want the total to appear could have as its control source something like:
=[Field1] + [Field2]
Every time you update one of the fields, the total will change. The same
expression in a report's text box should produce the same result. If you
use the report wizard you will just need to add your own unbound text box
for the calculation. Whether you use the wizard or create your own report,
you will need to use either a table or query as a record source.
You could also perform the calculation in a query. In that case you would
bind the text box to the calculated query field. I think you would need to
requery in the After Update event of all the controls bound to fields that
are part of the calculation.
Or you could use VBA. If you do so, it seems to me that the time to perform
the calculation is when the variables change.
 
Sorry about the confused terminology: I originaly meant an unbound text box.
I have learned quite a bit about calculated fields in the last couple days.
Originaly, I put the calculated field on a form, set its record source to
form level vb code that gathered its variables thru "me.controls", dlookups
with where conditions, etc... ,but this did not update when changes to values
were made unless I used a timer event or recalculated it thru events
everytime the user changed a value that affected it. I also found that when I
went to use it on a report that it got an error because it couldn't find the
variables,control values ,etc it needed to perform the calculation. So I
created a query which brought all the info I needed from various tables
together. In that query I made a calculated field and wrote a rather long and
confussing formula in the expression builder, which in my opinion was much
less user friendly, and more cyptic than writing it in vb at the form level,
however when I created a report based on the query my calculated field came
thru perfectly. I then made a form based on the query and the calculated
field showed up and updated when a value was changed(although you have to
move off the field, or press enter before it updates the value) Ditto for a
report based on the query. My conclusions are: That ,in general, the best
place to create a calculated field is in a query. However I really would like
it if there was an easy way to set its source to the return value of a vb
function and use keywords such as "me.control" etc to access the values I
want to use in it, and still have it work in reports,forms that are made from
it. I'm going to get it and when I do I'll post it here.
 
My conclusions are: That ,in general, the best
place to create a calculated field is in a query.
Click to expand...

An accurate conclusion.
However I really would like
it if there was an easy way to set its source to the return value of a vb
function
Click to expand...

Certainly; you can use a call to a VBA public function as a calculated
field in a Query. E.g. you can type

CalcField: MyFunction([thisfield], [thatfield])
and use keywords such as "me.control" etc to access the values I
want to use in it, and still have it work in reports,forms that are made from
it. I'm going to get it and when I do I'll post it here.
Click to expand...

Me.Control will NOT work in this scenario, since the Me keyword means
"the form which contains this code". If you want to call the function
from a Query or Report, Me is either unavailable or refers to the
wrong object.

What you can do - if the form named MyForm can be assured to be open -
is use

[Forms]![MyForm]![control]

in place of Me.control in your code. However, it should be possible to
do the calculation directly in the Query, using fields in the Query;
the only exception would be for an unbound textbox for user input of a
value which goes into the calculation but is not stored in any table.

John W. Vinson[MVP]
 
I have found another snafu in my logic regarding calculated fields. I had
said "the best
place to create a calculated field is in a query." My reasoning stemmed from
the fact that by putting the calculated field in the query it would show up
correctly in both forms or reports based on that query.This was the case when
the calculated field was done with the expression builder, but when I
replaced the formula with a public function call that passed arguments from
the query, I could no longer create new records in the form. I would get an
"Invalid use of null" error. I think the error came from the form trying to
perform the calculation with fields that had no values yet. I'm not sure.
(Any thoughts?)The prevailing wisdom might be to only use the expression
builder for calculated controls and in this simple example that theory would
work but I want to be able to use logic,conditional branches, values from
other tables, etc. At any rate here is my function code and the way I call it
from the query

Public Function performcalc(opr1, opr2, opr3) As Currency
performcalc = (opr1 + opr2) * opr3
End Function

TotalCost: performcalc([Value1],[Value2],[MaterialPriceModifier])

The first 2 of the parameters passed are from one table the 3rd,
MaterialPriceModifier is from a seperate table but is linked thru MaterialId
which is an autonumber field in the Materials table and a lookup field in the
1st table. Any ideas on why I get the error and how I could make this work?
 
I would get an
"Invalid use of null" error. I think the error came from the form trying to
perform the calculation with fields that had no values yet.
Click to expand...

Very likely. I'd suggest that you explicitly type your variables (as
Variants, which can accept Nulls) and, if appropriate, use the NZ()
function to convert nulls to zeros. Try changing the function to

Public Function performcalc(opr1 As Variant, opr2 As Variant, _
opr3 As Variant) As Variant
performcalc = (opr1 + opr2) * opr3
End Function

This will return NULL if any of the three arguments is null, otherwise
the expression.

John W. Vinson[MVP]
 
The variant idea worked but the calculated field didn't show up as currency.
The NZ() worked better with a little modification. It checks the values sent
which in a new record would all be null and sets them to 0. After the user
actually enters values the proper answer appears Heres my code do ya see any
flaws?

Public Function performcalc(opr1, opr2, opr3) As Currency
Dim varResult1, varResult2, varResult3 As Integer
varResult1 = Nz(opr1, 0)
varResult2 = Nz(opr2, 0)
varResult3 = Nz(opr3, 0)
performcalc = (varResult1 + varResult2) * varResult3

End Function

One draw back I saw was that as your entering field values on a new record in
the form your getting zero until you enter all 3 fields. This is not a big
issue as the user should realize that the result of the calculation should
not be 0. For now I guess it will suffice.
 
The variant idea worked but the calculated field didn't show up as currency.
The NZ() worked better with a little modification. It checks the values sent
which in a new record would all be null and sets them to 0. After the user
actually enters values the proper answer appears Heres my code do ya see any
flaws?
Click to expand...

I'd still explicitly type the arguments; they default to Variant but
it never hurts to be specific. Also you don't need the auxiliary
variables, you can use the arguments directly:

Public Function performcalc(opr1 As Variant, opr2 As Variant, _
opr3 As Variant) As Currency
performcalc = (NZ(opr1) + NZ(opr2)) * NZ(opr3)
End Function

Note that your Dim statement will dim varResult1 and varResult2 as
Variants, and that Integer is a bad choice - it will truncate $3.91 to
3, and then need to convert that (wrong) value back to Currency.

You might want to actually put in a check to see if any of the values
are NULL on input, and simply don't return anything if so:

If IsNull(opr1) Or IsNull(opr2) Or IsNull(opr3) Then Exit Function

before the performcalc line.

John W. Vinson[MVP]
 
Hi John, I like the idea of checking for null value and just exiting the
function on nulls.This makes the calculated value completly blank until all
fields are filled in.(wish I had thought of that). I have also changed the
code, added the typing to variant, and tested form,query, and report and they
all work fine. The power in this method of calculation is that the function
"perfomcalc" can get as complex as needed and should still perform as
expected.The modified function now is:

Public Function performcalc(opr1 As Variant, opr2 As Variant, opr3 As Variant)
As Currency
If IsNull(opr1) Or IsNull(opr2) Or IsNull(opr3) Then Exit Function
performcalc = (opr1 + opr2) * opr3
End Function

calculated field in query reads:

TotalCost: performcalc([Value1],[Value2],[MaterialPriceModifier])

Where Value1, Value2, and MaterialPriceModifier are fields in the query.

My ammended thoughts on calculated fields are:
1). In general, the best place to create a calculated field is in a query,
not in a table,form,or report unless its scope is limited to those objects.
2). If you want to set the calculated fields value to the result of a public,
module level, function, you must provide a means to deal with possibility of
null values being passed or the form will not function as designed. To be
specific you will get an "invalid use of null" error message.

John I'd like to thank you for your help in these posts. Hopefully the
information contained in these posts will be of help to someone else also.
This brings me to one rant I have about disscussion groups in general. That
is that searching posts for information is a tuff task due to the length of
the threads. Wouldn't it be nice if there was a way to sum up what was
learned complete with code snippets,summaries, and maybe attached sample test
databases and save them to a seperate post that would be condensed and in my
opinion much more useful and easyier to search?
 
Back
Top