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

hi Marcie,

bymarce wrote:
> 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 <--

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

"bymarce" wrote:

> 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

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

"Stefan Hoffmann" wrote:

> hi Marcie,
>
> bymarce wrote:
> > 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 <--
>

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

"David H" wrote:

> I would also add some logic that exits the sub/function if no email is found
> and advise the user appropriately.
>
> "Stefan Hoffmann" wrote:
>
> > hi Marcie,
> >
> > bymarce wrote:
> > > 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 <--
> >

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

"bymarce" wrote:

> 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

h Marcie,

David H wrote:
> 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 <--