Calculating something that will not be changing

[Guest]

In a form, I have a text box for weight and a textbox for height. With these
I'm needing to calculate BMI (Body Mass Index) with the equation:
=([Weight]/([Height]*[Height]))*703
in Control source of my form. As I've seen in multiple other postings,
people are saying you're not supposed to place a calculated value into a
table because it will change. I know that this value needs to only be
calculated once and will not be changing because we only care about the BMI
for the current day. If it would work better to not have this calculated
value show up in the form and only in the table, that is ok. Thanks for your
help.

 

[Wayne Morgan]

Actually, if you're storing Weight and Height, you don't need to store BMI.
As you've indicated, it can be calculated from the other two. Just calculate
it when you need it.

 

[Guest]

But I want the calculated BMI to show up in the table

Actually, if you're storing Weight and Height, you don't need to store BMI.
As you've indicated, it can be calculated from the other two. Just calculate
it when you need it.

--
Wayne Morgan
MS Access MVP

In a form, I have a text box for weight and a textbox for height. With
these
I'm needing to calculate BMI (Body Mass Index) with the equation:
=([Weight]/([Height]*[Height]))*703
in Control source of my form. As I've seen in multiple other postings,
people are saying you're not supposed to place a calculated value into a
table because it will change. I know that this value needs to only be
calculated once and will not be changing because we only care about the
BMI
for the current day. If it would work better to not have this calculated
value show up in the form and only in the table, that is ok. Thanks for
your
help.

 

[John Vinson]

But I want the calculated BMI to show up in the table

Why?

If you calculate it in a Query, you can: display it on a Form; print
it on a Report; sort by it; query by it; export it to Excel; ANYTHING
that you can do with it in a Table.

You can also do something with it which you CANNOT do with the value
stored in a table - *TRUST IT*. It would be perfectly possible to have
a height, weight, and BMI stored in a table, *and one of them may be
wrong*. Suppose you misrecorded the weight on data entry; calculated
an (incorrect) BMI; and then realized the error and reentered the
weight. The stored BMI will NOT change to reflect the correction.


John W. Vinson[MVP]

 

[fredg]

But I want the calculated BMI to show up in the table

Why? Nobody should ever see the table.

That BMI data can be calculated and displayed at any time in a query,
on a form, or in a report. Those can be viewed by users. If you view
the query, make the query Snapshot so no data can be changed.

** snipped **

 

[Guest]

I am doing this as a favor for someone and yes, people would see the table.
They want all the data in a table, I only created the form so it would be
easier to input the data.

 

[Rob Oldfield]

The short answer is that they shouldn't be allowed to see the table. See
commandment 2 here: http://www.mvps.org/access/tencommandments.htm

The slightly longer answer is just to show them the query (with the
calculation).

The best answer is to *only* allow them access via a form.

 

[Ed Warren]

To add to the discussion, you MEASURE height, you MEASURE weight, the BMI is
a calculated value. You should always store what is measured, not what is
calculated. Though it may be constant now, tomorrow (next year, next
decade) the BMI measurement calculation could change and your stored value
could not be 'recalculated'. If you have the measurements, you are fine,
rewrite the code and they are current.

Ed Warren.

 

[John Vinson]

In a form, I have a text box for weight and a textbox for height. With these
I'm needing to calculate BMI (Body Mass Index) with the equation:
=([Weight]/([Height]*[Height]))*703
in Control source of my form. As I've seen in multiple other postings,
people are saying you're not supposed to place a calculated value into a
table because it will change. I know that this value needs to only be
calculated once and will not be changing because we only care about the BMI
for the current day. If it would work better to not have this calculated
value show up in the form and only in the table, that is ok. Thanks for your
help.

OK... if you insist on doing it wrong... it can be done.

Use two textboxes, txtCalcBMI and txtBMI. The first would have the
expression as its Control Source; the second would be bound to the
table field.

In the Form's BeforeUpdate event put

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBMI = Me!txtCalcBMI
End Sub


John W. Vinson[MVP]

 

[Guest]

How many times has the calculation for BMI changed within the past 10 years.?
I don't care what BMI may stand for next year and I really don't think that
even if by some very small possibility that it did change, we'd care enough
to spend the time changing 250 different BMI's. Unfortunately, the whole
point of my post has been missed.

 

[Ed Warren]

My sincere apologies, do it your way, and enjoy.

Ed Warren.

 

[Guest]

Your "they" is different from my "they." This table is not being used in the
same way as a mySQL table hidden online. It is being used to hold
information about research participants and only one person is entering the
data and the participants have no access to any of the data, neither does
anyone outside the research study. She just wants it in the table so she can
print it later.

 

[Douglas J. Steele]

Rob's advice is correct: you shouldn't be exposing the table itself.

You shouldn't enter data directly into tables: you should always use a form.

You shouldn't print tables: you should always use a report.

 

[John Vinson]

Your "they" is different from my "they." This table is not being used in the
same way as a mySQL table hidden online. It is being used to hold
information about research participants and only one person is entering the
data and the participants have no access to any of the data, neither does
anyone outside the research study. She just wants it in the table so she can
print it later.

This may not have gotten propagated... reposting:

OK... if you insist on doing it wrong... it can be done.

Use two textboxes, txtCalcBMI and txtBMI. The first would have the
expression as its Control Source; the second would be bound to the
table field.

In the Form's BeforeUpdate event put

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBMI = Me!txtCalcBMI
End Sub


John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Well then can you get a calculated value from a form into a report?

 

[Douglas J. Steele]

Depends on the exact calculation. The simplest approach is to create a query
that included the calculation, so that the same query can be used as a
recordsource for both the form and report.

 

[Larry Daugherty]

Yes, so long as the form is loaded when you refer to it from the
report. But why? The query for the report will have all of the
fields from the table available to it and you can, once again,
calculate BMI where ever it needs to be shown. That's the way it's
done, Really!

I've been laughing myself silly reading this thread as people have put
in a lot of energy trying to educate you to the correct way to do
things with Access and with RDBMS in general Further, they have been
supporting their suggestions with reasons. Would you believe that
they're trying to HELP you, not constrain you. I endorse the
suggestion made by another respondent: visit www.mvps.ort/access and
read the COMMANDMENTS.. Bear in mind that everything you find on that
site was contributed by professional Access developers for the benefit
of other Access developers, neophyte through professional.

Rather than simply ignoring the respondents as they've attempted to
come to your aid, you might offer them a word of thanks. You have
received advice, freely and generously given by volunteers. Those
folks get some pretty good rates of pay in exchange for that same
intelligence during working hours.

Don't thank me. I've already been paid.

HTH

 

[fredg]

Yes, so long as the form is loaded when you refer to it from the
report. But why? The query for the report will have all of the
fields from the table available to it and you can, once again,
calculate BMI where ever it needs to be shown. That's the way it's
done, Really!

I've been laughing myself silly reading this thread as people have put
in a lot of energy trying to educate you to the correct way to do
things with Access and with RDBMS in general Further, they have been
supporting their suggestions with reasons. Would you believe that
they're trying to HELP you, not constrain you. I endorse the
suggestion made by another respondent: visit www.mvps.ort/access and
read the COMMANDMENTS.. Bear in mind that everything you find on that
site was contributed by professional Access developers for the benefit
of other Access developers, neophyte through professional.

Rather than simply ignoring the respondents as they've attempted to
come to your aid, you might offer them a word of thanks. You have
received advice, freely and generously given by volunteers. Those
folks get some pretty good rates of pay in exchange for that same
intelligence during working hours.

Don't thank me. I've already been paid.

HTH

Well said, Larry.
I count 10 responses all telling him the same thing.
It's so easy to do it correctly at the beginning, and so difficult to
'fix it' later.

 

[Douglas J. Steele]

I endorse the suggestion made by another respondent:
visit www.mvps.ort/access and read the COMMANDMENTS.

Or www.mvps.org/access <g>

 

[Larry Daugherty]

Hi Fred,

I sympathize with people just beginning with Access. They're told
that even a lot of things that seem to work shouldn't be done. A
large part of our problem is Microsoft; for two reasons: they want to
sell software, not necessarily to inform a new user to Access that
they have a long steep learning curve ahead. They also make Excel, an
excellent product in its own right. Most people are reasonably good
users of Excel before they venture into Access.. Usually, more
advanced learning in Excel doesn't require abandoning prior data
design, yady, yada. Because the appearance of a worksheet and a table
is so similar, people get sucked into making unwarranted assumptions
about behaviors.

Toughest of all is maintaining a humble attitude while people tell you
that you ought not to do something you very obviously can do. Another
factor that affects credibility is the flame wars that arise as PCD
Steve tries to victimize people who have posted hare seeking that
"free exchange of knowledge among peers" and everyone else tries to
convince Steve of the errors of his ways. To an outsider who doesn't
understand the history of the issue it can look like factional wars.

If Madison is as tenacious about finding and implementing the right
ways to get things done with Access as he has been about defending his
point of view he could become a good developer.

I hope he has begun to believe us and I hope that hundreds or
thousands of lurkers have gotten the benefits of our discussions.

Cheers,