create a new variable based on the existing variables

[leaf]

Hello,

I am trying to create a new variable called BMI using
weight and height variables. It did not work.
What is wrong? (my variables are weightpounds and
heightinches.)

Private Sub BMI_BeforeUpdate(Cancel As Integer)
BMI = "Weightpounds * 704.5 / (Heightinches *
Heightinches)"
End Sub

Thank you!

leaf

 

[John Vinson]

Hello,

I am trying to create a new variable called BMI using
weight and height variables. It did not work.
What is wrong? (my variables are weightpounds and
heightinches.)

Private Sub BMI_BeforeUpdate(Cancel As Integer)
BMI = "Weightpounds * 704.5 / (Heightinches *
Heightinches)"
End Sub

Thank you!

This is attempting to set BMI to a Text String containing the
expression. If you want to actually evaluate the expression, remove
the quotes. If Weightpounds and Heightinches are the names of controls
or fields on your form, use the Me! keyword to tell VBA that you're
talking about controls, not uninitialized VBA variables:

Me!BMI = Me!Weightpounds * 704.5 / Me!Heightinches ^ 2

 

[leaf]

Weight, height, and BMI are variables from a table and on
a form. Do I need to set the event procedure for weight
and hight variabbes?
I tried it and have not worked yet.

Thanks,
leaf

 

[Dan Artuso]

Hi,
The term 'variable' refers to things you have Dim'd
as in:

Dim BMI As Integer

Is that what you mean? They can't be stored in tables.
I suspect your terminology is throwing people off.

 

[leaf]

Thanks for the explanation of variables, fields and
controls. Weight, height and BMI are fields in a table
and they are controls on a form. The following are the
actual codes in VBA code.

Private Sub BMI_BeforeUpdate(Cancel As Integer)
BMI = Weightpounds * 704.5 / Heightinches ^ 2
End Sub

Thank you v. much.

leaf

 

[leaf]

Thank you very much for your response. I am a
statistician doing ACCESS for the first time.
I am trying some programming in ACCESS using what I know
in SAS.

I want weight, height and bmi stored in a table.
Dataentry people will enter data on a form. I hope when
they put in weight and height values, bmi(body mass index)
will be automatically calculated and show up on a form.

The following are codes:

Private Sub BMI_BeforeUpdate(Cancel As Integer)
BMI = Weightpounds * 704.5 / Heightinches ^ 2
End Sub

Thanks,
leaf

 

[John Vinson]

I hope when
they put in weight and height values, bmi(body mass index)
will be automatically calculated and show up on a form.

aha! A bit more background which I should have been able to intuit.

Sorry, but please ignore my previous advice. It won't work, because
the BMI control's BeforeUpdate will never fire unless the user types
something into BMI - which you don't want them to do.

I would suggest that your table field BMI *SHOULD NOT EXIST AT ALL*.
Since the value of BMI can be calculated on the fly from weight and
height, it *should* be calculated on the fly! Simply store the weight
and height in your table, and base the Form on a Query with your BMI
expression in a calculated field: just type

BMI: [Weightpounds] * 704.5 / [Heightinches] ^ 2

in a vacant Field cell, and use BMI as the control source of a textbox
on the form. It will calculate the BMI on the fly as the user enters
data into the other two fields; it will not be stored in the table but
there is no need for it to be stored as you can redo the calculation
whenever necessary.

 

[leaf]

I did try with Me! It did not work either.
Leaf

-----Original Message-----


Note that THIS IS NOT WHAT I SUGGESTED.

Please reread:

Me!BMI = Me!Weightpounds * 704.5 / Me!Heightinches ^ 2

The reason that it's not working is that, to VBA, Weightpounds HAS NO
MEANING. It's a variable which has not been declared in a Dim
statement, and has not been assigned a value. VBA *does not know* that
you mean it to refer to a table field or a form control until you tell
it so.

The Me!Weightpounds syntax means "look on the Form connected to this
Sub's module and return the value in the control named Weightpounds".
The Me! syntax *is required* for your code to work; that's why I
suggested it in my previous message.


.