Curious recordset error

[yoritomo]

Hi, I have a program that on a button click runs a query, and on completion decides to send an email to a recipient based on the amount of results.
However its crashing on trying to open the recordset, I have no idea why as I use an alomst exactly the same code segment in many of my other programs (admittedly though they are excel ones)

Dim stDocName As String
    Dim db As Database
    Dim rst As Recordset
    Dim qry As QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb
    
    strSQL = "SELECT [TblS&POrder].[Order Number], TblProblem.Problem, [TblS&POrder].Product, [TblS&POrder].Comment, TblProductCode.CELL" & _
             " FROM TblProblem INNER JOIN (TblProductCode INNER JOIN [TblS&POrder] ON TblProductCode.ITEM_NUMBER = [TblS&POrder].Product) ON TblProblem.ID = [TblS&POrder].Problem" & _
             " WHERE (((TblProductCode.CELL)='1' Or (TblProductCode.CELL)='2') AND (([TblS&POrder].[Email sent])=False) AND (([TblS&POrder].Problem)=2));"

    
    MsgBox strSQL
  '  Set qry = db.QueryDefs("qryEmailSentCellStockOut1+2")
    Set rst = db.OpenRecordset(strSQL)
    strSQL = "test"
    If rst.RecordCount > 0 Then
       stDocName = "RptEmailSentCellStockOut1+2"
    Else
        stDocName = "RptEmailSentCellStockOut3+4"
    End If



    stDocName = "RptEmailSentCellStockOut1+2"
    DoCmd.SetWarnings False
    DoCmd.SendObject acReport, stDocName, acFormatXLS, "cell@rothwell.co.uk", , , "Cell 1 StockOuts", "This report Contains Stockouts for Cell1 Products", 0
    DoCmd.SetWarnings True

Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

I cant for the life of me work out why it doesnt work

 

[namliam]

1) are you sure your query is correct?
2) Are you using ADO or DAO, if you dont know what that is... What version of access?

Greetz

 

[yoritomo]

Yup query is correct, I copied it straight from a created query that runs fine, and I just doubled checked it.

Erm, I think I'm using DAO, I'm on access 2000 as well, and I know the default is ADO, but I'm sure its DAO I'm using

 

[namliam]

Try using this:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qry As DAO.QueryDef

Disambigating might resolve your issue

 

[yoritomo]

Fantastic worked perfectly thanks!