Sending Email & Null Values (1 Viewer)

[WillJ]

Hi Guys,

Thanks in advance for your help with this. I've modified some code online and am by no means a programmer, but dabbled in the attempt.

I am getting a parameter value error, which from my research means their is a unassigned null value. I have input some code for the only var lookup statement I believe will return a null value however am still getting an error.

Also, as I say, I'm new and there may well be some other errors. If some eagle eye would be kind enough to look at what I'm doing and give me some advice, for something that may crop up after having variable matter sorted it'd be hugely appreciated.

Private Sub cmdSendEmail_Click()
On Error GoTo Err_cmdSendEmail_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stEmailID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim EmployeeID As String '-- Person who assigned ticket
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error


'-- Combo of names to assign ticket to
stWho = Me.cmdEmailTo
stWhere = "tblContacts.ContactID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[ContactEmail1]", "tblContacts", stWhere)

If IsNull(ContactEmail1) = True Then
variable = "Left blank"
End If

stSubject = "Remember to assign me to a request!"

stEmailID = Format(Me.EmailID, "00000")
RecDate = Me.EmailDate
'-- Helpdesk employee who assigns ticket
EmployeeID = Me.cmdEmployeeID.Column(1)

stText = Chr$(13) & "Email Reference: " & EmailID & Chr$(13) & _
"This email has been sent to you by: " & EmployeeID & _
Chr$(13) & "Sent On: " & RecDate & Chr$(13) & _
Chr$(13) & "This is an internal reference message"

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

'Set the update statement to disable command button
'once e-mail is sent
strSQL = "UPDATE tblEmail " & _
"SET tblEmail.EmailSent = -1 " & _
"Where tblEmail.EmailID = " & Me.EmailID & ";"


On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Me.EmailSent.Requery
Me.EmailSent.SetFocus
Me.cmdSendEmail.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdSendEmail_Click:
Exit Sub

Err_cmdSendEmail_Click:
MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

 

[TheSmileyCoder]

Comment out the "on error goto" on top, and try again, then tell us exactly which line the error occurs on.

 

[WillJ]

Thanks for the help smiley.

Run-time error '3464'

Data type mismatch in criteria expression.

Cont ...

varTo = DLookup("[ContactEmail1]", "tblContacts", stWhere)

 

[TheSmileyCoder]

If you get the error message and select debug, can't you tell which line of the code gives the error message? I could try to guess, but without knowledge of your system, I would be a wild guess at best. Getting the line that the error occurs on usually (although not always) is crucial to debugging.

 

[WillJ]

Hi smiley,

I now ... I realised that I hadn't posted it once I did ... it's;


varTo = DLookup("[ContactEmail1]", "tblContacts", stWhere)

Thanks

 

[TheSmileyCoder]

Ok. Much better

Often issue can arise when debugging code where the string variable for some reason does not look exactly like we expect. So if you use string variables as you do, and you run into trouble start by looking at the variables. Easiest way is to set a breakpoint at that line of code, execute and when the debugger stops at that line, hover the mouse over stWhere and look at the value contained in stWhere.

Normally when I use dlookup I never use:
stWhere = "tblContacts.ContactID = " & "'" & stWho & "'"
but instead
stWhere = "ContactID = " & "'" & stWho & "'"
I don't think this is the cause of your problem, but it should be easy enough to check. Is the bound column of Me.cmdEmailTo a numeric field? In that case try:
stWhere = "ContactID = " & stWho

 

[WillJ]

WOW ...
It worked, some seriously satisfying code their, that's amazing, thanks so much for you help.

 

[TheSmileyCoder]

Thats very nice to hear. Just to be 100% clear for "the next guy" reading this, which of the proposals solved the problem?

 

[WillJ]

Next guy ... this solved it
stWhere = "ContactID = " & stWho