Error 94 Invalid use of Null

H

Hugh self taught

Hi Guys & Gals with knowledge to help,

Below is a portion of code from my emailing form which I'm trying to build
some error prevention bits into.

At the .Subject= & or the .Body= statements I get an error "Run Time Error
94" "Invalid use of Null" & I can't figure out why it's not being trapped at
the If Me![Subject] = "" Then statement. I had it as If Me![Subject] = Null
Then with the same problem. If the fields are not blank then it runs through
fine but if I leave either blank then error.....

Set appOutlookRecip = .Recipients.Add(eMailAddress)
appOutlookRecip.Type = olTo 'Sets message to normal outgoing
e-mail message.
' Fill in the Subject line and main body of message.

If Me![Subject] = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Me![MessageBody] = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

.Subject = Me![Subject] 'Fill in the subject line.
.Body = Me![MessageBody] 'Fill in the message body.
 
P

Pendragon

Not an expert but try this - I've used it with success.

If IsNull(Me![Subject]) = True Or Me![Subject] Like "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

You can also verify with the Length function as well.

If Len(Me![Subject]) = Null Then ....... Length of Null text
strings returns Null

or

If IsNull(Len(Me![Subject])) = True Then

Hope that helps in some manner.
 
R

RonaldoOneNil

You are not exiting the routine so you will always get to your error line.
Try this

If Nz(Me![Subject],"") = "" Then
Msgbox ....
...SetFocus
Exit Sub
End If

If Nz(Me![MessageBody],"") = "" Then
Msgbox ...
....SetFocus
Exit Sub
End If
 
D

Dirk Goldgar

Hugh self taught said:
Hi Guys & Gals with knowledge to help,

Below is a portion of code from my emailing form which I'm trying to build
some error prevention bits into.

At the .Subject= & or the .Body= statements I get an error "Run Time Error
94" "Invalid use of Null" & I can't figure out why it's not being trapped
at
the If Me![Subject] = "" Then statement. I had it as If Me![Subject] =
Null
Then with the same problem. If the fields are not blank then it runs
through
fine but if I leave either blank then error.....

Set appOutlookRecip = .Recipients.Add(eMailAddress)
appOutlookRecip.Type = olTo 'Sets message to normal outgoing
e-mail message.
' Fill in the Subject line and main body of message.

If Me![Subject] = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Me![MessageBody] = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

.Subject = Me![Subject] 'Fill in the subject line.
.Body = Me![MessageBody] 'Fill in the message body.


If those fields are blank, their value is probably Null, not the empty
string "", and testing for = "" won't detect it. The error message bears it
out. You can test for (Null or "") in one statement like this:

If Len(Me![Subject] & "") = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Len(Me![MessageBody] & "") = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If
 
J

JimBurke via AccessMonster.com

Pretty sure you meant
If Len(Me![Subject] & "") = 0

rather than
If Len(Me![Subject] & "") = ""



Dirk said:
Hi Guys & Gals with knowledge to help,
[quoted text clipped - 27 lines]
.Subject = Me![Subject] 'Fill in the subject line.
.Body = Me![MessageBody] 'Fill in the message body.

If those fields are blank, their value is probably Null, not the empty
string "", and testing for = "" won't detect it. The error message bears it
out. You can test for (Null or "") in one statement like this:

If Len(Me![Subject] & "") = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Len(Me![MessageBody] & "") = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If
 
D

Dirk Goldgar

JimBurke via AccessMonster.com said:
Pretty sure you meant
If Len(Me![Subject] & "") = 0

rather than
If Len(Me![Subject] & "") = ""


Argh! You're right.
 
H

Hugh self taught

Hi Guys,

Thanks for that. I am getting to my msgbox now but the setfocus doesn't
occur. Am I missing or overlooking something?

Dirk Goldgar said:
Hugh self taught said:
Hi Guys & Gals with knowledge to help,

Below is a portion of code from my emailing form which I'm trying to build
some error prevention bits into.

At the .Subject= & or the .Body= statements I get an error "Run Time Error
94" "Invalid use of Null" & I can't figure out why it's not being trapped
at
the If Me![Subject] = "" Then statement. I had it as If Me![Subject] =
Null
Then with the same problem. If the fields are not blank then it runs
through
fine but if I leave either blank then error.....

Set appOutlookRecip = .Recipients.Add(eMailAddress)
appOutlookRecip.Type = olTo 'Sets message to normal outgoing
e-mail message.
' Fill in the Subject line and main body of message.

If Me![Subject] = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Me![MessageBody] = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

.Subject = Me![Subject] 'Fill in the subject line.
.Body = Me![MessageBody] 'Fill in the message body.


If those fields are blank, their value is probably Null, not the empty
string "", and testing for = "" won't detect it. The error message bears it
out. You can test for (Null or "") in one statement like this:

If Len(Me![Subject] & "") = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Len(Me![MessageBody] & "") = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
 
H

Hugh self taught

I can be an idiot sometimes. RonaldoOneNil had already pointed out the
problem fix. Not exit the sub.....

Hugh self taught said:
Hi Guys,

Thanks for that. I am getting to my msgbox now but the setfocus doesn't
occur. Am I missing or overlooking something?

Dirk Goldgar said:
Hugh self taught said:
Hi Guys & Gals with knowledge to help,

Below is a portion of code from my emailing form which I'm trying to build
some error prevention bits into.

At the .Subject= & or the .Body= statements I get an error "Run Time Error
94" "Invalid use of Null" & I can't figure out why it's not being trapped
at
the If Me![Subject] = "" Then statement. I had it as If Me![Subject] =
Null
Then with the same problem. If the fields are not blank then it runs
through
fine but if I leave either blank then error.....

Set appOutlookRecip = .Recipients.Add(eMailAddress)
appOutlookRecip.Type = olTo 'Sets message to normal outgoing
e-mail message.
' Fill in the Subject line and main body of message.

If Me![Subject] = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Me![MessageBody] = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

.Subject = Me![Subject] 'Fill in the subject line.
.Body = Me![MessageBody] 'Fill in the message body.


If those fields are blank, their value is probably Null, not the empty
string "", and testing for = "" won't detect it. The error message bears it
out. You can test for (Null or "") in one statement like this:

If Len(Me![Subject] & "") = "" Then
MsgBox (" Subject cannot be blank ") & vbCrLf & vbCrLf & _
Me!Subject.SetFocus
End If

If Len(Me![MessageBody] & "") = "" Then
MsgBox (" Message is empty ") & vbCrLf & vbCrLf & _
Me!MessageBody.SetFocus
End If

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top