Using Control Source to set Visible Status

[Guest]

Hello,

I have a diagram with the fields corresponding to the diagram as cells in a
table. For example, I have a diagram of the right arm and have the form tied
to a table with fields named "Palm", "Wrist", "Fingers", etc. Each of the
fields accepts a number between 1 and 10, where 0 is painless, up to 5 is
moderate pain, and greater than 6 is very painful. I have little shapes that
appear over the corresponding area on the diagram that should pop up if a
value is entered in the box. For example, if I enter a 9 in the "Wrist" box,
I want the black box to pop up over the wrist. I've been able to make this
work in the "After Update" box tied to the field but this is too high a level
- I think what I need to do is set the control source of the actual square,
right? What's happening is that the field most recently updated will
overwrite the diagram for ALL records.

So the blue square's control source would be something like: Iif(Text8 = 0,
Visible = False, Iif(Text8 < 6, Visible = True, Iif (Text8 > 6, Visible =
False))).

My example above (obviously) doesn't work...but is this close? What do I
need to do to fix?

 

[Douglas J. Steele]

You can't put the property name inside the Iif statement. You want something
like:

Me.BlueTextBox.Visible = Iif(Me.Text8 = 0, False, Iif(Me.Text8 < 6, True,
Iif (Me.Text8 > 6, False)))

or

Me.BlueTextBox.Visible = (Me.Text8 > 0) And (Me.Text8 < 6)

 

[Guest]

Ok, we're getting there!

1) My blue box is currently an OLE object with no "Control Source" setting.
Where would the code go?

2) I tried making a blue text box instead and inserting your code in the
"Control Source" but I continue to get the NAME? error.
EX: Me.RightWristBlue.Visible = (Me.Text8 > 0) And (Me.Text8 < 6)

 

[Guest]

Doug, I've updated the blue text box to read:

=Text17.Visible=([RightWrist]>0) And ([RightWrist]<6)

Now, if I enter a value of "2" in [RightWrist], the text box, instead of
just being blue, shows a value of -1. If I enter a value of "8" in
[RightWrist], the text box, instead of disappearing, shows a value of 0. What
have I done?!?

 

[Douglas J. Steele]

Where you put it depends on your form, but it's definitely not a control
source!

That's VBA code I gave you: it's intended to be inside an Event Procedure.
The most likely event would probably be the form's Current event: that fires
each time you advance from one record to another on a form.

 

[Guest]

It works!!! Thank you! I never knew about the "Current" feature...that
completely solved the problem. You truly are a MVP!