Conditional Visible property

[magmike]

I am attempting to show or hide a colored box, based on the value of a
field. Here is the code I used:

Private Sub Form_Load()
IIf CONTACTFIRSTNAME01 = Null, Me.Box01.Visible = False,
Me.Box01.Visible = True
End Sub

This is not working. What am I doing wrong?

Thanks in advance,
magmike

 

[Ken Snell \(MVP\)]

Nothing is ever equal to Null. You must use the IsNull function to test
whether a variable, field, or control contains the Null value. However,
sometimes what you have is an empty string, not a Null value, which will
produce a result indicating that the value is not the Null value.

Try this, the following code approach will test for both Null and an empty
string:

Private Sub Form_Load()
IIf Len(CONTACTFIRSTNAME01 & "") = 0, _
Me.Box01.Visible = False, _
Me.Box01.Visible = True
End Sub


Or, a shorter approach:

Private Sub Form_Load()
Me.Box01.Visible = (Len(CONTACTFIRSTNAME01 & "") <> 0)
End Sub

 

[magmike]

Nothing is ever equal to Null. You must use the IsNull function to test
whether a variable, field, or control contains the Null value. However,
sometimes what you have is an empty string, not a Null value, which will
produce a result indicating that the value is not the Null value.

Try this, the following code approach will test for both Null and an empty
string:

Private Sub Form_Load()
IIf Len(CONTACTFIRSTNAME01 & "") = 0, _
Me.Box01.Visible = False, _
Me.Box01.Visible = True
End Sub

Or, a shorter approach:

Private Sub Form_Load()
Me.Box01.Visible = (Len(CONTACTFIRSTNAME01 & "") <> 0)
End Sub

--

Ken Snell
<MS ACCESS MVP>









- Show quoted text -

Neither of those worked. The first, hid the box at all times, and the
other had no effect at all.

 

[BruceM]

Neither of those worked. The first, hid the box at all times, and the
other had no effect at all.

I think the Current event would be a better place for the code, assuming you
want the box's visible property to change from record to record depending on
the value in CONTACTFIRSTNAME01. The Load event runs once when the form is
opened. I'm not sure what record it looks at to determine the Visible
property you seek, but in any case the visible property won't change once
it's set unless there is code elsewhere to accomplish that.
Apologies to Ken if I misunderstand the documentation about IIf, but as far
as I know the IIf function needs parentheses:
IIf(Expression, Action if true, Action if false)

You can use If in VBA:
If Len(CONTACTFIRSTNAME01 & "") = 0 Then
Me.Box01.Visible = False
Else
Me.Box01.Visible = True
End If

The second shorter version of the code Ken provided should work (I assume
that by "no effect at all" you mean the box remained visible), but as
mentioned it needs to be in the Current event if the Visible property is to
be adjusted record-to-record. I'm not sure there is any reason to use <>
for Len (> should be enough), since I don't see how the length could ever be
less than zero. This is just an observation. I doubt it will change
anything.

 

[magmike]

I think the Current event would be a better place for the code, assuming you
want the box's visible property to change from record to record depending on
the value in CONTACTFIRSTNAME01. The Load event runs once when the form is
opened. I'm not sure what record it looks at to determine the Visible
property you seek, but in any case the visible property won't change once
it's set unless there is code elsewhere to accomplish that.
Apologies to Ken if I misunderstand the documentation about IIf, but as far
as I know the IIf function needs parentheses:
IIf(Expression, Action if true, Action if false)

You can use If in VBA:
If Len(CONTACTFIRSTNAME01 & "") = 0 Then
Me.Box01.Visible = False
Else
Me.Box01.Visible = True
End If

The second shorter version of the code Ken provided should work (I assume
that by "no effect at all" you mean the box remained visible), but as
mentioned it needs to be in the Current event if the Visible property is to
be adjusted record-to-record. I'm not sure there is any reason to use <>
for Len (> should be enough), since I don't see how the length could ever be
less than zero. This is just an observation. I doubt it will change
anything.- Hide quoted text -

- Show quoted text -

Thanks. You were right, they were working, however the first record in
the form wouldn't show that. I changed to the OnCurrent event, and now
it works fine. Thanks both of you!

 

[Ken Snell \(MVP\)]

I think the Current event would be a better place for the code, assuming
you want the box's visible property to change from record to record
depending on the value in CONTACTFIRSTNAME01. The Load event runs once
when the form is opened. I'm not sure what record it looks at to
determine the Visible property you seek, but in any case the visible
property won't change once it's set unless there is code elsewhere to
accomplish that.

Good catch, Bruce. I concur with you (my "excuse" is that I was tired when I

Apologies to Ken if I misunderstand the documentation about IIf, but as
far as I know the IIf function needs parentheses:
IIf(Expression, Action if true, Action if false)

Yes, this also is correct. See above "excuse"....

I'm not sure there is any reason to use <> for Len (> should be enough),
since I don't see how the length could ever be less than zero. This is
just an observation. I doubt it will change anything.

Either <> or > will work in this situation. I use them interchangeably with
this code example.

 

[Dale Fye]

Mike,

Don't know whether you understand the reason behind using

LEN(ContactFirstName01 & "") > 0

If you have a text field in your database that allows nulls, then the value
in that field may be Null (if it never had any data in it), or it could be an
empty string (if it had data in it at one time that was simply deleted), or
it could be a string of some length.

Based on your original post, one might be inclined to use :

me.Box01.Visible = NOT ISNULL(ContactFirstName01)

However, if the value in ContactFirstName01 is an empty string "", then
IsNull will return False, and the box will be displayed. On the other hand,
the LEN function will return an error if you pass it a NULL value, so to
check to see whether the field is NULL or a zero length string, you have to
use syntax that will not generate an error, thus, the need to concatenate a
zero length string ("") to the field name inside the LEN( ) function.

HTH
Dale

 

[magmike]

Mike,

Don't know whether you understand the reason behind using

LEN(ContactFirstName01 & "") > 0

If you have a text field in your database that allows nulls, then the value
in that field may be Null (if it never had any data in it), or it could be an
empty string (if it had data in it at one time that was simply deleted), or
it could be a string of some length.

Based on your original post, one might be inclined to use :

me.Box01.Visible = NOT ISNULL(ContactFirstName01)

However, if the value in ContactFirstName01 is an empty string "", then
IsNull will return False, and the box will be displayed. On the other hand,
the LEN function will return an error if you pass it a NULL value, so to
check to see whether the field is NULL or a zero length string, you have to
use syntax that will not generate an error, thus, the need to concatenate a
zero length string ("") to the field name inside the LEN( ) function.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.






- Show quoted text -

Thanks for that. I was planning on looking into this whole LEN thing.
I do like understanding how it works so I don't have to play the hunt,
copy, paste game.