Error 94: Invalid Use of Null

[athomas8251]

Assistance please! I have a comment field that in some instances, always requires the user to enter a note.



There is one instance however, when notes shouldn't be entered.

Case 1, 2, 6 work great.

I never had a Case 5 and users always enter notes into it. Here's what I am trying to do:

If the 'status' comment has anyting in it, show a message, select the text (for cut/paste), then once text is removed, allow the record to be added.

Here's the VBA. I highlighted what I was trying to do.

 
 
Private Sub AddButton_Click()
Dim MatTotal As Variant ' current material total
Dim CurCode As String ' current referral code
Dim db As Database ' current DB
Dim rs As Recordset ' tActivity
 
[[ bunch of stuff snipped that doesn't apply ]]
 
Select Case Me.Activity ' get activity type code
Case 1, 2, 6 ' IB, OB, or Update/Close
If IsNull(Me.ActivityComment) Or Me.ActivityComment = "" Then
MsgBox "You must enter the Activity Comment."
Me.ActivityComment.SetFocus ' goto field
Exit Sub ' bail out
End If '
Me.ActivityComment.SetFocus ' go to action field
Me.ActivityComment.SelStart = 0 ' set insertion point to start of text
Me.ActivityComment.SelLength = Len(Me.ActivityComment) ' selecte all Text
DoCmd.RunCommand acCmdSpelling ' spell check field
 
MY ATTEMPT: 
 
Case 5 ' Status comment
If Not IsNull(Me.ActivityComment ???????????
MsgBox "You cannot enter a comment on Status. Cut and paste to IB or OB note instead"
Me.ActivityComment.SetFocus ' goto field
Exit Sub ' bail out
End If '
Me.ActivityComment.SetFocus ' go to action field
Me.ActivityComment.SelStart = 0 ' set insertion point to start of text
Me.ActivityComment.SelLength = Len(Me.ActivityComment) ' selecte all Text

Thanks for looking at this!

Andi

[June7]

Test for Null should be enough.
If Not IsNull(Me.ActivityComment) Then

You want to copy to clipboard? Check http://bytes.com/topic/access/answer...pboard-via-vba

And if need to, set textbox to Null
Me.ActivityComment = Null

[boblarson]

If you don't have ALLOW ZERO LENGTH set to NO in that field then you should really use

If Len(Me.ActivityComment & vbNullString) > 0 Then

[athomas8251]

Thank you Bob. That worked. Problem solved!