Return different values in "text box2" based on value of "text box1"

[Jon]

I have Text Box1 that returns a value based upon numeric calculation on
another field.
I would like Text Box2 to display a different message based upon the
result returned in Text Box1.

I can accomplish this with IIF but I need to respond to several ranges
of numbers rather than just apply true/false to the result from Text
Box1.

Thanks for any suggestions.

 

[Jeff L]

Is this something you could accomplish with a Case statement in the
After Update Event or On Current?

Select Case txtBox1
Case 1 to 4
Do Something
Case 5-10
Do Something else
etc
End Select

 

[Jon]

Jeff,

I've tried your suggestion in a couple of ways but I get no output to
Text Box2.

Any other ideas?

Jon

 

[Jeff L]

Maybe if you could show me what you tried I can help you better. Post
your code and I'll take a look.

 

[Jon]

Jeff,

Thank you for your patience. I am in the early stages of self-learning
Access/VBA
I've tried several variations, this is my most recent.

************************************************************
Private Sub Form_Current()
Select Case Me!txtBMI
Case Is >= 18.5 <= 25
Me!txtBMIComment = "Ideal Weight"
Case Is > 25 <= 30
Me!txtBMIComment = "Overweight"
Case Is > 30
Me!txtBMIComment = "Obese"
Case Else
Me!txtBMIComment = "Underweight"
End Select

End Sub

************************************************************

Notes:
-txtBMI is a calculated value... =([weight]*703)/[Height]^2
-the main form is in Single Form view

Jon

 

[Jeff L]

Try this instead:

Select Case Me!txtBMI
Case 18.5 To 25
Me!txtBMIComment = "Ideal Weight"
Case 25 To 30
Me!txtBMIComment = "Overweight"
Case Is > 30
Me!txtBMIComment = "Obese"
Case Else
Me!txtBMIComment = "Underweight"
End Select

 

[Jon]

Jeff,

Thanks again. That works but I have 2 follow-up questions.
1. What if the calculated BMI falls right on 25?

Case 18.5 To 25
Me!txtBMIComment = "Ideal Weight"
Case 25 To 30
Me!txtBMIComment = "Overweight"

Is there a way to, in effect say:

Case 18.5 to <25
Me!txtBMIComment = "Ideal Weight"
Case 25 To 30
Me!txtBMIComment = "Overweight"

2. How can I cause the form to immediately change the
[txtBMIComment] when the calculated field [txtBMI] changes? It
currently will only update if the form is closed and then re-opened.

 

[Jeff L]

1. The case statement looks at your criteria in sequence until it
finds one that fits the value being compared. So if it is 25, your
txtBMIComment should say Ideal Weight because that is the first
criteria that fits that value.

2. Here's what I suggest. Make a new function in a module like this:
Public Function GetBMIComment(txtBMI As Double) As String

Select Case txtBMI
Case 18.5 To 25
GetBMIComment = "Ideal Weight"
Case 25 To 30
GetBMIComment = "Overweight"
Case Is > 30
GetBMIComment = "Obese"
Case Else
GetBMIComment = "Underweight"
End Select

End Function

Now in the txtBMIComment field of your form, look at the properties,
click the data tab and in Control Source put =GetBMIComment(txtBMI)
Now whenever the value of the calculation changes, so will the comment.

Hope that helps!

 

[Jon]

As you suggested, I created a new module called GetBMIComment with the
following code:

Public Function GetBMIComment (txtBMI As Double) As String

Select Case txtBMI
Case Is < 15
GetBMIComment = "(Starvation, less than 15)"
Case 15 To 18.5
GetBMIComment = "(Underweight, 15-18.5)"
Case 18.5 To 25
GetBMIComment = "(Ideal Weight, 18.5-25)"
Case 25 To 30
GetBMIComment = "(Overweight, 25-30)"
Case Is > 30
GetBMIComment = "(Obese, 30-40)"
Case Else
GetBMIComment = "(Morbidly Obese, >40)"
End Select

End Function

And I have set the txtBMIComment > Data > Control Source to:
=GetBMIComment([txtBMI])

But now the field just shows: #Name?

 

[Jeff L]

Here are some things to check for. This is off MS website:

Access displays #Name? in a control when the name that you supplied as
the source of the control's value is not valid. You use the
ControlSource property to specify the source of the control's value.
For example, you might have misspelled the name, or the source might
have been renamed or deleted. You may also see #Name? in a control if
you place an expression in the control's ControlSource property and you
insert a space before the equal sign that starts the expression.

In other words you have something misspelled, the name of the value you
are trying to pass is not called txtBMI, or there is a space in front
of the = in the Control Source.

Also, just so you know, your Case Else will never be true because you
have IS > 30 right before it and 90 > 30 so it will fall into that
category instead of the Else. You would need to put 30 to 40 instead
of Is > 30.

 

[Jon]

Jeff,

Thank you once again for your expert help.

Not knowing how to work with modules, I had simply added the new module
in the main database window.

I found that what I needed to do was to open the form in design view,
click the "Code" icon on the toolbar, then type in the GetBMIComment
module code as you recommended.

The form now works perfectly.

Jon