Email coding - Urgent!!

[sha7jpm]

Old Topic - New Problem!!

can anyone help with the following syntax? Tim kindly helped previously but time is running out so would be very keen for any pointers at all.

I am receiving a "invalid use of Null" error when I run the code.
have played around with the code but to no avail.

can anyone see why the problem exists?

below is the code.

ta

John

>>>>>>>>>>>>>>>>>>>>>>>>>>

Option Compare Database
Option Explicit

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim strTextMessage As String
strTextMessage = "THE FOLLOWING INSTRUMENTS HAVE NOW BEEN COMPLETED FOR" & Chr(13)
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & Me!txt1a & " - " & Me!txt1d & Chr(13)
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt1b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt1c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt1f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt2b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt2c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt2f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt3b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt3c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt3f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt4b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt4c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt4f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt5b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt5c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt5f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt6b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt6c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt6f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt7b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt7c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt7f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt8b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt8c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt8f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt9b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt9c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt9f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt10b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt10c, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt10f, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)

DoCmd.SendObject acSendNoObject, , , Me!txtemailaddressCC, Me!nfer1, , "Project Code " & Me!txt1a, strTextMessage, True

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub
Private Sub textline2()

End Sub

Private Sub Text26_BeforeUpdate(Cancel As Integer)

End Sub

Function fIsBlank(strField As String, I As Integer, _
Optional strString2 As String) As String
Dim strR As String

If strField <> "" Or Not IsNull Then
Select Case I
Case Is = 1
strR = "Instrument Description : -" & " " & strField & strString2
Case Is = 2
strR = "SNAP FileName : -" & " " & strField & strString2
Case Is = 3
strR = "Instrument Description : -" & " " & strField & strString2
End Select
Else
strR = ""
End If

fIsBlank = strR

End Function

 

[Travis]

You don't tell us where the Invalid Use of Null occurs

Invalid use of Null will occur when you try to set a String Variable = Null.

Have you tried stepping through your code to see at what line the Invalid Use of Null occurs? Use break points and the Debug Menu to walk through the code.

Try using the Nz function (Null to Zero) around each of your text fields that you are passing to fIsBlank.

or Change stField as String --> stField as Variant

 

[sha7jpm]

the invalid use of null occurs when the text boxes are checked for content and the fIsBlank routine is called.

I tried the variant idea but still had a compiler error.

if the boxes are all strings can I not just remove the Is null statement?

when I try this it still does not run, and I still get the same error message even though I dont declare a null command..!

 

[Travis]

Dim'ing stField as a Variant should allow you to pass a null value to the fIsBlank function. What kind of Compile error are you getting?

 

[sha7jpm]

missed a variant!

travis

many thanks

missed changing one of the strings to a variant...

so it works perfectly now.

am very appreciative of all your help.

kind regards

John