Help: The expression you entered as a query parameter produced....

[Guest]

The message says it produced this.
The object doesn't contain the automation object ' stsubmitter."

Thanks for the help in adavance. Without this board I would never this stuff!


Function NotifyClosure()
On Error GoTo Err_NotifyClosure

Dim varTo As Variant '-- Address for SendObject
Dim varCC As Variant '-- Address for CC
Dim stText As String '-- E-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stDesc As String '-- Reference task description
Dim stSubmitter As String '-- Person whom submited Task
Dim stprimary As String '--Primary person assigned
Dim stresolution As String '--Primary person assigned
Dim strpolicy_number As String '--policy number


'-- Declare the analyst,backup and submitter'
stSubmitter = Me.Entered_by
stprimary = Me.Assigned_to
strpolicy_number = Me.Text81

'Declare the resolution from from
stresolution = Me.Analyst_Involvement

'-- Looks up email address from Tbl_Users--'
'Declare person the email is address to to
varTo = DLookup("[Email_Addr]", "[tbl_User]", "[tbl_User]![User_name]=
stSubmitter ")
'Declare the first person to cc
varCC = DLookup("[Email_Addr]", "[tbl_User]", "[tbl_User]![User_name]=
stprimary")
'Declare the email subject line
stSubject = " Your department Research Item for " & strpolicy_number & "
has been Closed"
'Declare the Task Description
stDesc = Me.Task__Description
'Declare the Task Number
stTicketID = Format(Me.Task_number, "0000")

'-- The entire block below contains the email message
stText = stprimary & " has closed this task." & Chr$(13) & _
"Task number: " & stTicketID & " has been closed today " & Chr$(13) & _
"The final resolution for policy number " & strpolicy_number & " is:" &
Chr$(13) & _
stresolution


'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1

'Tell user this is successful'

'Exit
Err_NotifyClosure_Exit:
Exit Function


Err_NotifyClosure:
MsgBox Error$
Resume Err_NotifyClosure_Exit

End Function

 

[John Vinson]

The message says it produced this.
The object doesn't contain the automation object ' stsubmitter."

You need to fix some quotes. In the line

varTo = DLookup("[Email_Addr]", "[tbl_User]",
"[tbl_User]![User_name]= stSubmitter ")

it's looking for an OBJECT named stSubmitter; since the variable name
is inside the quotemarks, Access assumes it's something in the table.

You'll need to move the variable outside the quotemarks, and delimit
it with quote marks (" is Chr(34)) which are required for searching
text fields:

varTo = DLookup("[Email_Addr]", "[tbl_User]",
"[User_name]=" & Chr(34) & stSubmitter & Chr(34))

This will translate the criterion to something like

[User_Name] = "DODGEP"

John W. Vinson[MVP]