strTo Email = DLookup not working

[JNariss]

Hello,

I am trying to use the SendObject to send an email to the manger who is
filling out the form and it is not working. I am using this as my code:

Private Sub Command49_Click()
Dim strTo As String
Dim strRequest_ID As String
Dim strMessage As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strComments As String
Dim strEstCompDate As String
Dim strRequestor_Name As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * From Request WHERE [Request ID]" &
" = " & Forms![Approval Form]![Request ID])

strRequest = rst![Request ID]
'strTo =
"(e-mail address removed);[email protected];[email protected]"
strTo = DLookup("Email", "User", "User_ID = " & Me.UserID)
strComments = Me.Approval_Comments
strEstCompDate = Me.EstCompDate
strRequestor_Name = Me.Requestor_Name

strMessage = "Request ID: " & strRequest & " has been denied." &
Chr$(13) & Chr$(13) & _
"Please forward this to: " & strRequestor_Name & Chr$(13) & Chr$(13) &
_
"Comments: " & strComments & Chr$(13) & Chr$(13) & _
"Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , strTo, , , "Analyst Assigned",
strMessage, No, False
DoCmd.Close acForm, "Approval Form", acSaveYes
MsgBox "You have notified the Analyst that he/she has been assigned to
a Request and to go to the database to view it.", vbOKOnly, "Analyst
Assigned"


End Sub



The table that holds the email address is called User and the email
field is called Email. I also have User ID and Full Name within that
table.

On my form I have a list box called ApproverName which pulls the
managers full names from a query I set and also populates the text
field on the form (User ID) with their matching User ID from the User
table.

Perhaps someone could help me out here b/c i can't seem to figure this
one out.

Thanks in advanced,
Justine

 

[Joshua A. Booker]

Justine,

One thing if is your user_id field is text you need quotes around the
criteria like this:

strTo = DLookup("Email", "User", "User_ID = '" & Me.UserID &"'")

After this line insert:

debug.print strTo

This will print the result f the dlookup to the debug window to help you
debug the lookup.

HTH,
Josh

 

[JNariss]

Hello,

Thanks for the reply. I inserted the code and included the debug
statement. I keep getting an error of:

runtime error '94

Invalid use of Null

Could this be happening b/c a field is not filled in on the form?

 

[Joshua A. Booker]

Justine,

The dlookup is probably not returning any results. Do you see the word Null
in the debug window after you run the code? (CTRL+G to open debug window)

Are you sure there is a user in the table with that user_id?

Josh