Dlookup invalid use of null

[bymarce]

Why isn't this Dlookup working? I want this code to look up an email address
in he personel table based on the initials used in an unbound combo box.
When I run it it says "invalid use of null". Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
SendTo = DLookup("Email", "Personel", "Initials = ""Me.fAssignedTo""")
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
SendTo = Mail
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments", , SendTo, , ,
MySubject, MyMessage, False

End Sub

 

[Stefan Hoffmann]

hi Marcie,

When I run it it says "invalid use of null". Thanks.

SendTo = DLookup("Email", "Personel", "Initials = ""Me.fAssignedTo""")

There are two errors, it must be

SendTo = Nz(DLookup("Email", "Personel", _
"Initials = '" & Replace(Me.fAssignedTo, "'", "''") & "'"))

DLookup returns NULL, if doesn't find a match or if the matching field
contains NULL.

The data type String cannot be NULL. You had a mismatch because your
condition was wrong.

Use

MsgBox "Initials = ""Me.fAssignedTo"""

to see what condition you had used.


mfG
--> stefan <--

 

[BeWyched]

Probably cos your DLookup statement doesn't make sense. You have incorrect
"'s and, I suggest you use the Nz function to make sure the system doesn't
bomd-out if DLookup can't find a match.

Try changing it to:

SendTo = Nz(DLookup("Email", "Personel", "Initials = '" & Me.fAssignedTo &
"'"), "")

(where '" = exagerated, ' ", and "'" = " ' ")

I would then check SendTo to make sure it doesn't = ""

I also note that later on you change the value of SendTo without using the
DLookup value?

Cheers.

BW

 

[David H]

I would also add some logic that exits the sub/function if no email is found
and advise the user appropriately.

 

[bymarce]

Thanks for the advise. I plan to add that in.
Marcie

 

[bymarce]

Thanks for the help. I did get the quotes worked out. Now I need to be able
to send this to a group rather than one person. How do I use the forms
filter string to make a recordset to use in the "openrecordset" part of this
code?

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close

 

[Stefan Hoffmann]

h Marcie,

I would also add some logic that exits the sub/function if no email is found
and advise the user appropriately.

Private Sub Email_Work_Click()

Dim SentTo As String

SendTo = Trim(Nz(DLookup("Email", "Personel", _
"Initials = '" & Replace(Me.fAssignedTo, "'", "''") &
"'")), "")

If Len(SendTo) > 0 Then
'Send Email
Else
MsgBox "no reciever."
End IF

End Sub


mfG
--> stefan <--