CODEWITHSUNDEEP
ms-accessvbadao
Hawsidog
Hawsidog

Reputation: 165

querydefs run-time error 3265: item not found in this collection

In Access 2007 I'm trying to send the results of a query however, I keep receiving the error "Run-time error '3265': Item not found in this collection." The error is coming up on the line:

Set qry = CurrentDb.QueryDefs(ReportQueryName)

I've checked spelling on the fields and I've tried messing with the Tools>References to make sure that I have the correct library.

This is my current code:

Private Sub Command202_Click()

Dim qry As DAO.QueryDef
Dim strSQL As String
Dim ReportQueryName As String

ReportQueryName = "ReportEmail"

Set qry = CurrentDb.QueryDefs(ReportQueryName)
strSQL = "SELECT [ID], [title] FROM Cases WHERE ID = " & Me.ID
qry.SQL = strSQL

DoCmd.SendObject acSendQuery, "ReportEmail", acFormatXLSX, "[email protected]", ..., , False

End Sub

Upvotes: 2

Views: 7319

Answers (1)

Mark Butler
Mark Butler

Reputation: 895

You can't use QueryDefs to create a new query - you have to use CreateQueryDef instead:

Private Sub Command202_Click()

    Dim qry As DAO.QueryDef
    Dim strSQL As String
    Dim ReportQueryName As String

    ReportQueryName = "ReportEmail"

    strSQL = "SELECT [ID], [title] FROM Cases WHERE ID = " & Me.ID

    Set qry = CurrentDb.CreateQueryDef(ReportQueryName,strSQL)

    DoCmd.SendObject acSendQuery, "ReportEmail", acFormatXLSX, _
    "[email protected]", ..., , False

End Sub

Sometimes your new query will not show up in the Access windows straight away.

If you want it to, you can use:

Application.RefreshDatabaseWindow

Upvotes: 4

Related Questions