Form not acting as Expected

[Dave14867]

Hello All,

I have a form in a new database I am working on and it isn't acting as expected. What I want it to do is check to see if a field contains something and if it does, make it visible, and if it doesn't make it invisible, the code I am using is below.

It doesn't work however, If I make everything not visible on the form "visible" attribute, everything stays invisible. What am I missing? I haven't seen this type of behavior before. If I put in a stop in the code and check the values while it's running, the values of Ingredient1, 2 & 3 are not null however 4 is null as expected.

Private Sub Form_Current()



If Me.Ingredient1 <> Null Then
Me.Ingredient1.Visible = True
Me.Ing1Qty.Visible = True
End If

If Me.Ingredient2 <> Null Then
Me.Ingredient2.Visible = True
Me.Ing2Qty.Visible = True
End If

If Me.Ingredient3 <> Null Then
Me.Ingredient3.Visible = True
Me.Ing3Qty.Visible = True
End If

If Me.Ingredient4 = Null Then
Me.Ingredient4.Visible = False
End If


End Sub


Thanks in advance

Dave

[pbaldy]

That test won't work, think of Null as unknown. Nothing is equal to it, or not equal to it. This tests for both Null and a zero length string:

If Len(Me.Ingredient1 & vbNullString) > 0 Then

[Dave14867]

pbaldy,

Thanks so much, I'll give that a try in the AM

Thanks

Dave

[pbaldy]

No problem Dave, post back if you get stuck.

[John_G]

In addition to Paul's note - Each of the tests in your code must have an ELSE block in them to indicate what to do if the fields ARE Null (blank). The visible / invisible properties of the form controls are not reset when you move to another record; the code has to do that. So, using this and Paul's suggestion, your first test might look like this:

If Len(Me.Ingredient1 & vbNullString) > 0 Then
  Me.Ingredient1.Visible = True
  Me.Ing1Qty.Visible = True
Else
  Me.Ingredient1.Visible = False
  Me.Ing1Qty.Visible = False
End If

[Dave14867]

Thanks guys it worked famously, just as expected.

John_G, I did originally have the else statement in there but after I got so frustrated with it not working I started to rewrite everything and hadn't put that back in, my foolish mistake.

I am curious though, why didn't just the check for Null work, like I said, it was seeing that the item was not null?

Thanks again
Dave

[pbaldy]

If you think of Null as unknown, you see that nothing can be compared to it, even another unknown. The test for it could use the IsNull() function, but most of us also test for a zero length string.

[pbaldy]

Forgot Jason has a faq on Nulls, etc:

http://www.baldyweb.com/NullEmptyEtc.htm