simple (if / end if) question - my code is wrong.. but where? ;/

G

Gary

I'm trying to check to see if the user hasn't entered anything in two
text box controls.

First i tried the following: -

================================

If txblead.Value = "" And txbcompany.Value = ""
Then MsgBox "A lead without a contact? please try again"
Exit Sub
End If

=================================

this generated an error, so i thought perhaps i needed null instead of
a "" - so i tried the following: -

==================================

If txblead.Value Is Null And txbcompany.Value Is Null
Then MsgBox "A lead without a contact? please try again"
Exit Sub
End If

==================================

this generates an error to.

i need to check if BOTH fields are blank, and if they are then display
my message box and exit the code. If they are not both blank then
continue with the execution of my sub...

what am i doing wrong please?

thanks,

Gary.
 
S

SusanV

Hi Gary,
Try this:

If IsNull(Me.txblead) And IsNull(Me.txbcompany) Then
MsgBox "A lead without a contact? please try again"
Exit Sub
End If
 
G

Gary

Hi i'm afraid its still not right.

Although i'm not getting an error now, it's not doing what i want it to
do.
the code is running and despite both fields are empty i'm getting no
msg. box, instead the code is just continuing on past my little if/end
if block, regardless - I think the problem is "empty" doesn't mean
'null' in text boxes? any ideas anyone?
 
S

SusanV

Your original code (not using IsNull but rather using = "") was missing the
Me reference to evaluate the textboxes:

If Me.txblead.Value = "" And Me.txbcompany.Value = "" Then
MsgBox "A lead without a contact? please try again"
Exit Sub
End If

Perhaps this will work for you now.
 
G

Gary

hmm ..

it's still happily just going straight past my code and not displaying
a msgbox even though both boxes are empty. they are both text boxes, i
can't understand this i thought it would be simple.

thanks for your help susan - but im still stuck!

the form in question is a subform on my switchboard, does that make any
difference?

gary
 
P

Peter J. Veger

1. "... IS NULL" is SQL operator, IsNull(...) is VBA function call
2. It is not apparent whether both db-fields are NULL (value not known) or
"" (value known, namely an empty string)
(maybe you should use the VBA-function NZ which transforms a text-NULL into
a "")
 
G

Gary

How do i use it can you give me the code to paste into my database?

i don't know about that function. but my requirement is simplicity
istself!
i have two text boxes on a form called LMS. that form is a subform on a
switchboard.

i have a button on the LMS form. I want to check to see if both
'txblead' and 'txbcompany' have had anything typed into them by the
user. If they both haven't had anything typed into them by the user I
want to display a msg box, and return to the form. If one of the two
have had something typed into them, then I want to execute some other
code.

Somewhere along the way that very simple requirement has been lost
amidst a sea of nulls, vba's, sql's, and strings. My poor coding
skills are too blame. But i am still here three days later not knowing
how to check to see if two text boxes are empty, and if they are, NOT
DO an action, if they are not DO an action.

Any help is greatly appreciated.

Gary.
 
S

SusanV

Oh i just noticed the "value" part - you don't need that. Fixing that may do
the trick:

If Me.txblead = "" And Me.txbcompany = "" Then
MsgBox "A lead without a contact? please try again"
Exit Sub
End If



To add the debugging:

In the VBE (development window) open the debugging window to see the
results.

Dim lead as string
Dim company as string

lead = me.txblead
debug.print lead

company = txbcompany
debug.print company

If Me.txblead = "" And Me.txbcompany = "" Then
MsgBox "A lead without a contact? please try again"
Exit Sub
End If
 
P

Peter J. Veger

1. Textbox controls normally have as source a Text field in a table.
In the definition of the table, you may find the "General" properties of the
field:
it may be Required or not, it may Allow Zero Length or not.
If it is Required, it will never be NULL (users are forced to provide a
value).
If it allows zero length, its value may be "", otherwise users are forced to
provide a text with positive length.
The value NULL normally means: value unknown, not provided.

2. The value of a textbox is a Variant that can represent NULL or a string.
If you want to single out the NULL value, you use IsNull(....);
if you want to single out the value "", you use ....= "";
if you want distinguisch NULL and "" from other values, you use Nz(,,,, ,
"")
(or shorter: Nz(...) because "" is the default value for the 2nd
parameter)

3. You tested:
C1 = "" And C2=""
and
IsNull(c1) And IsNull(c2)
but maybe C1 is NULL and C2=""
So in this case
Nz(C1)="" And Nz(C2)=""
will help.
 
Top