When is a Null not a Null?

[chris]

I have a form which is used for adding and entering data depending on
where it is called from. When called in add mode (acFormAdd) a blank
form is openned.

When the cursor enters the text box [Name] on the form field I am
trying test whether it is empty so that a search form can be openned
if a new name is to be added. (I don't want the search form to open in
cases when the form already has data in and I am editing rather than
adding data). The form is based on a query and the data displayed in
[Name] is the result of a calculated field in the query which
concatenates firstname and surname from a table.

I have tried this

Private Sub Name_Enter()
If IsNull(Me![Name]) = True Then
code if name is blank
End If
End Sub

and this

Private Sub Name_Enter()
If Me![Name] = "" Then
code if name is blank
End If
End Sub

to test for the text box being blank but neither works. I can only
suppose that because the contents of text box is based on a
calculation it is not actually Null or empty when nothing is displayed
in it. Can anyone explain more about this and suggest a way in which I
can perform a test to show if it is "empty" in these circumstances?
Chris

 

[kingston via AccessMonster.com]

When are you executing this? Put in a debug stop and watch for the value of
the field. Otherwise, try using the functions Len() and Nz().

I have a form which is used for adding and entering data depending on
where it is called from. When called in add mode (acFormAdd) a blank
form is openned.

When the cursor enters the text box [Name] on the form field I am
trying test whether it is empty so that a search form can be openned
if a new name is to be added. (I don't want the search form to open in
cases when the form already has data in and I am editing rather than
adding data). The form is based on a query and the data displayed in
[Name] is the result of a calculated field in the query which
concatenates firstname and surname from a table.

I have tried this

Private Sub Name_Enter()
If IsNull(Me![Name]) = True Then
code if name is blank
End If
End Sub

and this

Private Sub Name_Enter()
If Me![Name] = "" Then
code if name is blank
End If
End Sub

to test for the text box being blank but neither works. I can only
suppose that because the contents of text box is based on a
calculation it is not actually Null or empty when nothing is displayed
in it. Can anyone explain more about this and suggest a way in which I
can perform a test to show if it is "empty" in these circumstances?
Chris

 

[Keith Wilby]

I have a form which is used for adding and entering data depending on
where it is called from. When called in add mode (acFormAdd) a blank
form is openned.

When the cursor enters the text box [Name] on the form field I am
trying test whether it is empty so that a search form can be openned
if a new name is to be added. (I don't want the search form to open in
cases when the form already has data in and I am editing rather than
adding data). The form is based on a query and the data displayed in
[Name] is the result of a calculated field in the query which
concatenates firstname and surname from a table.

I have tried this

Private Sub Name_Enter()
If IsNull(Me![Name]) = True Then
code if name is blank
End If
End Sub

and this

Private Sub Name_Enter()
If Me![Name] = "" Then
code if name is blank
End If
End Sub

to test for the text box being blank but neither works. I can only
suppose that because the contents of text box is based on a
calculation it is not actually Null or empty when nothing is displayed
in it. Can anyone explain more about this and suggest a way in which I
can perform a test to show if it is "empty" in these circumstances?
Chris

Hi Chris.

For starters, don't use "Name" because it's a reserved word. Second, don't
use "!" to refer to a text box, use ".". Third, give your controls
meaningful prefixes - a lot of developers use "txt" for text boxes - it
helps make your code more understandable. So ... rename your calculated
field FullName, your text box txtFullName and try

If Me.txtFullName = "" Then

BUT ... is your calculated field really ever empty? Won't there be a space
and/or comma in it?

Keith.
www.keithwilby.com

 

[Guest]

Don't use Name as a name. It is an Access reserved word. Uninitialized
controls are Null unless either at the table or form level you have defined a
default value or if by change a user enteres a space in the control. To test
for either Null or and empty string:

If Trim(Nz(Me.txtName,vbNullString)) = vbNullString Then

 

[Douglas J. Steele]

<picky>
There's nothing wrong with using ! rather than . when referring to a
control.

In fact, technically it's the correct syntax (while . isn't).
</picky>

 

[Keith Wilby]

<picky>
There's nothing wrong with using ! rather than . when referring to a
control.

In fact, technically it's the correct syntax (while . isn't).
</picky>

You surprise me Doug. Not only do you lose Intellsense with Bang, you also
lose compiling's ability to identify missing controls. It's Dot for me
every time

Keith.

 

[Keith Wilby]

<picky>
There's nothing wrong with using ! rather than . when referring to a
control.

In fact, technically it's the correct syntax (while . isn't).
</picky>

OK, having just read this

http://www.dbforums.com/archive/index.php/t-1259776.html

I concede that Bang is considered to be correct. However, since I prefer
the Dot method and haven't suffered any ill-effects (yet) I'll continue so
to do

Regards,
Keith.

 

[Douglas J. Steele]

You surprise me Doug. Not only do you lose Intellsense with Bang, you
also lose compiling's ability to identify missing controls. It's Dot for
me every time

I didn't say I don't use .

I was simply commenting that there's nothing wrong with using !. <g>

 

[chris]

Thanks for the advice on my particular problem and for the exchanges
on bang vs dot. I had never really understood the reasons for use of !
when it seemed that dot would do anyway and seems to work in all
circumstances.
Chris