mattturley
asked on
Invalid Use of Null with a hyperlink field
OK, this one has me about to throw the computer out the window.
I am sending a report from a form using a command button. The message body will include a link to the document associated with the report - I am pulling that from a hyperlink field called FilePath. Sometimes that path will be empty, and I can't figure out how to handle it.
Here is the onclick for the command button.
Private Sub Command42_Click()
On Error GoTo Command42_Err
Dim strFilePath As String
strFilePath = Mid(Me.FilePath, 4, Len(Me.FilePath) - 4)
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information related to the following link. <\\pasa27dc01\office shares" & strFilePath & ">", True
Command42_Exit:
Exit Sub
Command42_Err:
MsgBox Error$
Resume Command42_Exit
End Sub
I've tried every freaking combination of If - Else, and If - ElseIf statement I can think of and always get either a data type mismatch, an object required, or, most frequently, an Invalid Use of Null error.
Please - send some advice or a small explosive device this way.
I am sending a report from a form using a command button. The message body will include a link to the document associated with the report - I am pulling that from a hyperlink field called FilePath. Sometimes that path will be empty, and I can't figure out how to handle it.
Here is the onclick for the command button.
Private Sub Command42_Click()
On Error GoTo Command42_Err
Dim strFilePath As String
strFilePath = Mid(Me.FilePath, 4, Len(Me.FilePath) - 4)
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information related to the following link. <\\pasa27dc01\office shares" & strFilePath & ">", True
Command42_Exit:
Exit Sub
Command42_Err:
MsgBox Error$
Resume Command42_Exit
End Sub
I've tried every freaking combination of If - Else, and If - ElseIf statement I can think of and always get either a data type mismatch, an object required, or, most frequently, an Invalid Use of Null error.
Please - send some advice or a small explosive device this way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jimhorn...
this is what I ended up using.
If Len(Nz(Me.FilePath, "")) > 0 Then
'There's a URL with it...
strFilePath = Mid(Me.FilePath, 4, Len(Me.FilePath) - 4)
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information related to the following link. <\\pasa27dc01\office shares" & strFilePath & ">", True
Else
'There's not a URL with this.
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information.", True
End If
Only difference was that you had not set the strFilePath variable.
Thanks again!
For my (and other's) education... what was most likely causing the Invalid Use of Null?
this is what I ended up using.
If Len(Nz(Me.FilePath, "")) > 0 Then
'There's a URL with it...
strFilePath = Mid(Me.FilePath, 4, Len(Me.FilePath) - 4)
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information related to the following link. <\\pasa27dc01\office shares" & strFilePath & ">", True
Else
'There's not a URL with this.
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information.", True
End If
Only difference was that you had not set the strFilePath variable.
Thanks again!
For my (and other's) education... what was most likely causing the Invalid Use of Null?
Cool beans man.
Null is when there's nothing in a field. It's different from an empty string "", and it's a pain in the a#@ to handle. In a field such as URL, where you can't really force the user to enter one if none exists, the best you can do is trap it using the Nz function (Null-to-zero), and handle it accordingly.
When doing table design, the more fields you set to .Required = True, the less headache this will be, although sometimes it isn't possible, like a person's Alias if they really have none.
Glad I can help.
-Jim
Null is when there's nothing in a field. It's different from an empty string "", and it's a pain in the a#@ to handle. In a field such as URL, where you can't really force the user to enter one if none exists, the best you can do is trap it using the Nz function (Null-to-zero), and handle it accordingly.
When doing table design, the more fields you set to .Required = True, the less headache this will be, although sometimes it isn't possible, like a person's Alias if they really have none.
Glad I can help.
-Jim
If IsNull(Me.FilePath) Then
'The file path is null, do something
Else
'It isn't empty, send the message.
strFilePath = Mid(Me.FilePath, 4, Len(Me.FilePath) - 4)
DoCmd.SendObject acSendReport, "rptMemoSend", "SnapshotFormat(*.snp)", , , , "Executive Secretariat Tasking", "Please see the attached document for tasking information related to the following link. <\\pasa27dc01\office shares" & strFilePath & ">", True
End If