Type Mismatch Error (1 Viewer)

[Novice1]

I keep getting a "type mismatch" error. I initially thought it was because of a bad email address but after deleting all the records but two (good email addresses), I still get the error. What could be causing this?


Dim rs As Recordset
Dim vRecipientList As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryCritiques1")

If rs.RecordCount > 0 Then
rs.MoveFirst

Do

If Not IsNull(rs!email) Then
vRecipientList = vRecipientList & rs!email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

DoCmd.SendObject acSendNoObject, , acFormatTXT, rs!email, , , "Customer Service Critique", "Sir/Ma'am," & vbCr & vbCr & _
"According to our blah, blah.", False

 

[Alc]

Which line causes the error?

Where do you use vRecipientList? I see you declare it and set values to it.

 

[pbaldy]

On what line? The recordset is at EOF when you refer to it in SendObject. Use the variable.

 

[Novice1]

I'm sorry. I used the variable. I pasted the wrong test. Here's the whole code. Not sure what line is causing the problem.

Private Sub Command594_Click()

On Error GoTo Error_Handler

Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

Dim varX As Variant
varX = DLookup("ReturnEmail", "tblCritiques", "CritiqueID = 1")

Dim varY As Variant
varY = DLookup("SignatureLine1", "tblCritiques", "CritiqueID = 1")

Dim varZ As Variant
varZ = DLookup("SignatureLine2", "tblCritiques", "CritiqueID = 1")


Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryCritiques1")

If rs.RecordCount > 0 Then
rs.MoveFirst

Do

If Not IsNull(rs!email) Then
vRecipientList = vRecipientList & rs!email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

DoCmd.SendObject acSendNoObject, , acFormatTXT, vRecipientList, , , "Customer Service Critique", "Sir/Ma'am," & vbCr & vbCr & _
"According to our records you visited the Military Personnel Flight, Customer Service Section on . We would " & _
"truly appreciate it if you would answer a few questions for us so we can recognize our superior performers and identify improvement opportunities. " & vbCr & vbCr & _
"1. Please rate the following:" & vbCr & vbCr & _
" a. Quality of Service: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" b. Courtesy of Staff: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" c. Knowledge of Staff: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" d. Appearance of Staff: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" e. Waiting Time: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" f. Condition of Facility: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
" g. Overall Experience: Very Good, Good, OK, Poor, or Very Poor" & vbCr & vbCr & _
"2. Additional Comments:" & vbCr & vbCr & vbCr & _
"Thank you for taking the time to complete this survey. Please return the critique to " & varX & vbCr & vbCr & "vr" & vbCr & vbCr & vbCr & varY & vbCr & varZ, False

Else

MsgBox "No one in queue"

End If


Error_Handler_Exit:
On Error Resume Next
Exit Sub

Error_Handler:
MsgBox Err.Description
Resume Error_Handler_Exit

 

[Alc]

You could either debug the code, or add message boxes at intervals and see between which two boxes the error occurs. Without knowing which line is the problem one, it's a lot harder to identify the issue.

 

[Minty]

Turn off the error handling - then you will see where your error is

 

[Novice1]

That was helpful. Tools/Options/General/BreakonAllErrors


This is the line I'm getting the error. I checked the query name (ok). I checked the email field (ok). What could cause the Type Mismatch Error?

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryCritiques1")

 

[pbaldy]

Does the query open without error? If so, disambiguate the declaration (actually should be done anyway):

Dim rs As DAO.Recordset

 

[Novice1]

Thank you very much that fixed it!!

 

[pbaldy]

Happy to help!