When is a Null not a Null?

When is a Null not a Null?

Post by chri » Fri, 06 Jul 2007 22:28:16



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
 
 
 

When is a Null not a Null?

Post by kingston v » Fri, 06 Jul 2007 22:53:06

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().



--
Message posted via AccessMonster.com
http://www.yqcomputer.com/

 
 
 

When is a Null not a Null?

Post by Keith Wilb » Fri, 06 Jul 2007 22:53:53


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
 
 
 

When is a Null not a Null?

Post by S2xhdHV » Fri, 06 Jul 2007 23:16:02

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

--
Dave Hargis, Microsoft Access MVP
 
 
 

When is a Null not a Null?

Post by Douglas J. » Fri, 06 Jul 2007 23:22:14

<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>

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no e-mails, please!)
 
 
 

When is a Null not a Null?

Post by Keith Wilb » Sat, 07 Jul 2007 00:11:12


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.
 
 
 

When is a Null not a Null?

Post by Keith Wilb » Sat, 07 Jul 2007 00:21:02


OK, having just read this

http://www.yqcomputer.com/

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.
 
 
 

When is a Null not a Null?

Post by Douglas J. » Sat, 07 Jul 2007 00:22:33


I didn't say I don't use .

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

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no e-mails, please!)
 
 
 

When is a Null not a Null?

Post by chri » Tue, 10 Jul 2007 22:32:59

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