How to check IsNull(Me.txtAirReason.Value)?

[Angus]

Hi experts,

I have some experience in excel vba but a newbie in access, now i have a
access code to request users input a reason (link to reason table) when they
select air shipment mode. However the code doesn't check and show the message
as expected.

Me.txtAirReason.Value = ""

If UCase(Me.cboShipmentMode.Value) = "AIR" And IsNull(Me.txtAirReason.Value)
= True Then
MsgBox "Air shipment reason cannot be blank when Shipment Mode is AIR",
vbCritical
Me.txtAirReason.SetFocus
Exit Sub
End If

However if I change IsNull(Me.txtAirReason.Value) = False

Then it shows the message. It seems to me that there is something as a
txtAirReason value. My question:

1. I have already make txtAirReason.Value = "", why there is still
"something" inside?

2. In excel I can run the program step by step by F8, how to do the same
thing in Access, so that I can go to the code to see what is the value?

3. Anyone can help me to fix the code?

 

[tina]

a zero-length string is NOT the same thing as Null. though i don't see why
you are setting txtAirReason to a zero-length string ("") to begin with -
don't you *want* the user to enter a reason?

note that Access is not case-sensitive, so there's no need to use the
UCase() function. there's no need to refer to the Value property of the
controls, either, as that is the default property in control references.
also, you didn't say what event you're running this code from. my suggestion
would be to run it from the form's BeforeUpdate event procedure, so you can
cancel the record update until the requirement is met, as

If Me!cboShipmentMode = "AIR" And _
IsNull(Me!txtAirReason) Then
Cancel = True
Msgbox "Shipment Mode is AIR, so enter a reason, please."
Me!txtAirReason.SetFocus
End If

hth

 

[Angus]

It is the code on a form, when users input data and click the button, it
checks for the compulsory data and alerts if none. The code:

Me.txtAirReason = ""

If UCase(Me.cboShipmentMode.Value) = "AIR" And
IsNull(Me.txtAirReason.Value) = True Then
MsgBox "Air shipment reason cannot be blank when Shipment Mode is AIR",
vbCritical
Me.txtAirReason.SetFocus
Exit Sub
End If

DoCmd.GoToRecord , , acNewRec
End Sub

Someone wrote the code and I just add a field "txtAirReason".

I try to replace with your code and the same thing happens, the code doesn't
check and moves ahead. However, when I change to

If (Me.cboShipmentMode) = "AIR" And (Me.txtAirReason) = "" Then

I get what I expect. I just don't know why IsNull doesn't work, but it works
fine when it's checking other fields.

Also just want to learn more:

What is the difference between Me.txtAirReason and Me!txtAirReason?

 

[tina]

If (Me.cboShipmentMode) = "AIR" And (Me.txtAirReason) = "" Then

I get what I expect. I just don't know why IsNull doesn't work, but it works
fine when it's checking other fields.

i already told you why, hon. a zero-length string and Null *are NOT the same
thing*. since your code explicitly sets the value of txtAirReason to a
zero-length string (""), the value IS NOT NULL.

What is the difference between Me.txtAirReason and Me!txtAirReason?

here's a copy of an explanation of "dot vs bang" by MVP Dirk Goldgar, from
an Oct 27, 2005 post in microsoft.public.access.formscodding ng, which can
also be found in Google groups at
http://groups.google.com/group/micr...+author:goldgar&rnum=2&hl=en#7ea1e64b5d2c256d

my personal preference is to use bang (!) when refering to objects that *i*
add to a form or report, and dot when refering to object properties.

*************************************

Here's my stock answer on the subject:

--------- BANG (!) vs. DOT (.) ----------
It's not so much a question of one or the other being "proper syntax",
but that they mean different things that nevertheless almost always give
the same result. As I understand it, the bang (!) notation specifically
denotes that what follows is a member of a collection; in this case, a
member of the form object's default collection, the Controls collection.
The dot (.) notation denotes that what follows is a property or method
of the preceding object. That would logically make the bang notation
"proper" and the dot notation improper.


But wait. Wherever possible, Access makes the controls on a form and
the fields in its recordsource all available as properties of the form.
It also makes the fields of the recordsource available via the bang
notation. I'm not sure exactly how it does this; maybe if a name is
not found in the Controls collection it checks the Fields collection of
the form's recordset as a fallback position. So for most practical
purposes Me!ControlName and Me.ControlName evaluate to the same thing,
and the timing tests I've seen suggest that there is little to choose
between them as far as execution efficiency is concerned. I seem to
recall that there is a very slight difference, but I can't remember
which way the advantage lies, and it's not much. There's a coding-time
advantage, however, to using the dot notation, as it makes the
"intellisense" dropdown lists available. That's a strong argument for
using the dot notation, in my book.


But wait again! I said above that Access makes the controls available
as properties "wherever possible". There are cases where it can't do
that. Specifically, it can't do it when there is already a property of
the same name as the control in question. For example, if your form
"Form1" has a control or a field foolishly named "Name", currently
displaying the value "John Doe", then executing this statement in the
form's code module:


Debug.Print Me!Name, Me.Name


will print


John Doe Form1


in the Immediate Window. So you must be careful not to use any reserved
words or built-in properties as names for your controls, if you want to
use the dot notation to refer to them. But then, you should avoid doing
that anyway, as it tends in general to confuse poor Access.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com


(please reply to the newsgroup)

*************************************