Type Mismatch Error

[newone]

I'm a newbie to coding and am having a problem with code that was given to me to modify for my particular use. My goal is to email a particular page of a report based upon an ID (Named SPID). I have the report created and underlying queries and it works fine. Now the problem... I was given sample code of how to do this but when I modify for my use I get the following error: "Run-time error '13' - Type Mismatch." While I think I know what a type mismatch is, I can't find any reason for it in the code. My code is below. If anyone can set me straight on what is going on, I would greatly appreciate. BTW, all variables in the queries are string variables. The highlighted line in debugger is:Set RstTmp = db.OpenRecordset(SqlStr, dbOpenSnapshot)


Private Sub cmdEmail_Click()

Dim db As Database
Dim RstTmp As Recordset
Dim Loop1 As Long
Dim SqlStr As String

Set db = CurrentDb()

SqlStr = "SELECT qryMerged.CustomerID, qryMerged.SPID, qryMerged.EmailTest, qryMerged.Customer, qryMerged.Type, qryMerged.CustCity, qryMerged.CustSt FROM qryMerged GROUP BY qryMerged.CustomerID, qryMerged.SPID, qryMerged.EmailTest, qryMerged.InstallDate, qryMerged.CustSt;"

Set RstTmp = db.OpenRecordset(SqlStr, dbOpenSnapshot)

DoEvents
On Error Resume Next
RstTmp.MoveLast
RstTmp.MoveFirst
On Error GoTo 0

If RstTmp.RecordCount = 0 Then
MsgBox "There are no events"
Exit Sub
End If

For Loop1 = 1 To RstTmp.RecordCount
Me!txtBox.Value -RstTmp!SPID

DoCmd.SendObject acSendReport, "rptWorksheetRental", acFormatRTF, _
RstTmp!SPID, , , "Test Subject", "Test Message", False
RstTmp.MoveNext
Next Loop1

MsgBox (Loop1 - 1) & " Messages sent"

db.Close

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub

 

[Pat Hartman]

There's nothing obvious in the query string. Does qryMerged run without error?

 

[rich.barry]

You may pick up something if you try checking your sql statement by making a new blank query, going to View..SQL, pasting your statement into the sql window, then try to run it.
I always find it easiest when making sql's for code to make them in design view, get them working, then switch to sql view, copy the statement and then paste it into my code.

(But maybe thats how everyone does it??)


[This message has been edited by rich.barry (edited 09-17-2001).]

 

[newone]

I found the problem but don't know the where's or why's of it. I moved MS DAO 3.0 Object Library to the top in references and this solved it. Before I did this, MS ActiveX Data Obj 2.1 Lib was ahead of it.

While this solved the problem of actually getting the code to execute, another problem came out of it. The original code was supposed to email only the page in the report that corresponds to the particular company. Instead it sends all pages of the report to each company. If anyone knows how to modify the code to accomplish this, I would appreciate any guidance.

[This message has been edited by newone (edited 09-18-2001).]

 

[Pat Hartman]

I would use a newer library than 3.0. You should see 3.6 in the list of available libraries. If you are not using any ADO code in the database, having the DAO library higher in the reference list than ADO 2.1 should be fine. If you use both ADO and DAO, you will need to have the ADO 2.1 library have a higher presidence in the reference list than the DAO 3.6 library. In that case, you would need to qualify any DAO objects with DAO. For example:
Dim db As DAO.Database
Dim RstTmp As DAO.Recordset