Data Type Mismatch

[timmy]

Hello again everyone, sorry for posting yet another question but i really need a second set of eyes here...

The following code is designed to send an email when a button is pressed, then disable the button and tick the checkbox.

Im getting an error "Data type mismatch in criteria expression." Access is more than helpful as usual with its error messages and only allows me to press ok or press the red "x" .

Private Sub emailbutton_Click()
On Error GoTo Err_emailbutton_Click
Dim stWhere As String 
Dim varTo As Variant 
Dim stText As String 
Dim RecDate As Variant 
Dim stSubject As String 
Dim stTicketID As String 
Dim stWho As String 
Dim strHelpDesk As String 
Dim strSQL As String 
Dim errLoop As Error
 
stWho = Me.EmployeeID
stWhere = "Employees.EmployeeID = " & "'" & stWho & "'"
 
varTo = DLookup("[Email]", "Employees", stWhere)
stSubject = ":: Workorder Confirmation from  ::"
stTicketID = Format(Me.WorkorderID, "9999")
RecDate = Me.DateReceived
 
strHelpDesk = Me.EmployeeID.Column(1)
 
stText = "Your Workorder has been processed." & Chr$(13) & _
Chr$(13) & "Reference Number: " & stTicketID & Chr$(13) & _
"Processed by: " & strHelpDesk & _
Chr$(13) & "Received Date: " & RecDate & Chr$(13) & _
Chr$(13) & "This is an automated message." & _
" Please do not reply to this e-mail."
 
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
 
strSQL = "UPDATE Workorders " & _
"SET Workorders.emailbutton = -1 " & _
"Where Workorders.WorkorderID = " & Me.WorkorderID & ";"
 
On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0
 
 
Me.emailcheck.Requery
Me.emailcheck.SetFocus
Me.emailbutton.Enabled = False
Exit Sub
Err_Execute:
 
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
 
strSQL = "UPDATE Workorders " & _
"SET Workorders.emailbutton = -1 " & _
"Where Workorders.WorkorderID = " & Me.WorkorderID & ";"
 
Exit_emailbutton_Click:
Exit Sub
Err_emailbutton_Click:
MsgBox Err.Description
Resume Exit_emailbutton_Click
End Sub

Now something i thought might affect it was that i connect to a MySQL server through linked tables, the checkbox "emailcheck" has its datasource set to a bloon field (tinyint(1)) in MySQL. I haven't written any additional code to tell access what to do with the data from MySQL, could this be causing a problem? I sort of guessed access would know what to do, naively.

I know its a big read but any help would be appreciated, this is the biggest task ive attempted with VBA and i would be so chuffed if i got this working!

[ajetrumpet]

go your OPTIONS in the vba editor:

tools>options>general tab>

and check the BREAK ON ALL ERRORS radio button.

I think that highlights the error and stop on it. Not 100% sure though.

check that first.

[pbaldy]

You can temporarily comment out the

On Error GoTo ...

lines and run the code. Then you should be offered a Debug button, which will take you to the offending line.

[timmy]

thanks for your replies .

the culprit is line 17:

varTo = DLookup("[Email]", "Employees", stWhere)

the email field is just a varchar(50), any ideas whats wrong?

[ajetrumpet]

I don't know if you can use a variable in the 3rd arg of a dlookup, bud.

but if you can, it needs to be changed anyway. try:

stWhere = "[EmployeeID] = " & stWho

if you were going to enter the info into that arg with an actual number value instead of a variable, it would have to look like that

[timmy]

that solved that problem, thanks!

Getting a 2501 error "The SendObject action was cancelled" now... nothings ever simple aye? It's on line 31 this time :

DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

most likely a problem with my args again...

[ajetrumpet]

that error is more complicated. might have to look at a file to figure that one out

[timmy]

ok, ill upload a data-less version of my DB tomorrow. Thanks so much for your help, really hope i get this problem sorted too .

[ajetrumpet]

ok, ill upload a data-less version of my DB tomorrow. Thanks so much for your help, really hope i get this problem sorted too .

include step by step instructions sir, otherwise I will not look at it.

It's courtesy, that's all. thanks

[timmy]

just rechecked the VBA, i think i lent on the keyboard at some point... thanks so much for your help. looks like its working!