Formula writing in access using male-female dropdown menu

[Mac rod]

hi,

I'm writing a simple database that uses a drop down menu with male-female values. what i want to do is when a user chooses "male" the 'result text box' will use a formula different as when he chooses "female". The formula is for calculating body fat percentage.

Thanks in advance...

[munkee]

You want to use a select case statement using VBA.

In your result textbox you will need to check what value has been selected in the dropdown menu.

For example you would use something like this attached to the onclick event of a "Calculate" button:

Select Case me.dropdownname
Case "Male"
Me.txtBoxResult = maleformulaetc
Case "Female"
Me.txtBoxResult = femaleformulaetc
Case Else
msgbox "You must select a gender", vbinfromation,"Error"
End Select

[Mac rod]

Thanks for your reply. but how about if i only want the answer to be calculated automatically in a text box without having to use a "calculate" button. like if i fill up 3 other text box that corresponds to 3 variables in the formula the "result" text box will show the answer either for "male" or "female".

[Rabbit]

Then use the on change or after update event.

[Mac rod]

Hi!

Thanks again. Yup i could use that, definitely! the code builder. i'll try that. and get back to you.

[Mac rod]

hi,

i tried the on change and after update event but the text box shows nothing. this is the code. please help.

Private Sub bodyfat__AfterU pdate()

Select Case Me.Gender
Case "Male"
Me.bodyfat% = "maleformul a"
Case "Female"
Me.bodyfat% = "femaleform ula"
End Select

End Sub

[munkee]

Can you describe the setup of your form.

text boxes / names etc

[Mac rod]

in my form i have the combo box ("male","female ") i also have text boxes for input of height, weight etc then i have the text boxes which displays the result such as the BMI and bodyfat. the BMI is ok i just used the expression builder in the control source, my problem is the bodyfat as it has 2 different formula for male and female which i want to automatically calculate when i chose either male or female. Tnx again.

[Mac rod]

combo box = [gender]
result textbox = [bodyfat%]
in the [bodyfat%] textbox i want to display a number based on the other textbox such as height and gender etc calculated with a formula that is different for male and female.

[munkee]

It depends entirely on the order of your textboxes on the page as to what event you should place the code in.

As an example:

Height
Weight
Gender

In the onchange event of gender:

Select case me.gender
Case "Male"
me.bodyfat% = 1.2 * me.txtHeight * me.txtWeight
Case "Female"
me.bodyfat% = 1.5 * me.txtHeight * me.txtWeight

Case Else
'do nothing
End Select

Everytime you change the value of the combobox it should recalc based on the formula.

[Rabbit]

% is the mod operator in VB. Use Me.[bodyfat%]

[Mac rod]

Still doesn't show any values. Here is the complete code...

Private Sub bodyfat__AfterU pdate()

Select Case Me.Gender
Case "Male"
Me.bodyfat% = 1.2 * Me.height * Me.weight
Case "Female"
Me.bodyfat% = 1.5 * Me.height * Me.weight

Case Else
'do nothing
End Select

End Sub

i even tried me.[bodyfat%] as rabbit suggested. I placed this code in the after update event of the textbox [bodyfat%].

[Rabbit]

You need to put it in the after update event of the gender selection because that's when you need it to change. Not when they change body fat % since I assume they can't change that number.

[Mac rod]

Finally!!! I've tried placing the other codes in the combo box before and still it didn't work but this one is working perfectly. Thanks a lot to both of you Munkee and Rabbit. One more thing, if I want to be a little bit ambitious and want to put a calculate button can you help me with the code because i have a couple of result text box that have their own formula like bmi and bodyfat%. its the same database only this time with a button. thank you very much in advance.

[Rabbit]

The code's the same. You're just putting it in the Click event of the button instead.