3265 - Item Not Found In This Collection

[egewitz]

I keep getting this error and it's driving me crazy:
3265 - Item Not Found In This Collection
I'm using MS Access 2003. Through VBA I'm attempting to fill in an excel spreadsheet with a query based on a table. Here is my code (setting the QueryDef is causing the error, see text in bold/italics):

    Dim MyExcel As Excel.Application
    Dim MyWorkbook As Excel.Workbook
    Dim MyWorksheet As Excel.Worksheet
    Dim strMyWorkbook As String
    Dim strSQL As String
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
 
    ' Open the MonthlyBreakdown spreadsheet
    strMyWorkbook = GetPathOrName(GetDatabasePathAndName()) & "\Copy of MonthlyBreakdown.xls"
    Set MyExcel = New Excel.Application
    Set MyWorkbook = MyExcel.Workbooks.Open(strMyWorkbook)
    Set dbs = CurrentDb()
 
    strSQL = "SELECT DISTINCTROW Left([tblTransaction].[Treaty],6) AS Treaty6, " & _
             "Sum(tblTransaction.txnPremium1) AS BasePrem, " & _
             "Sum(tblTransaction.txnAllowance1) AS BaseAllow, " & _
             "Sum(tblTransaction.txnPremium2) AS ADBPrem, " & _
             "Sum(tblTransaction.txnAllowance2) AS ADBAllow, " & _
             "Sum(tblTransaction.txnPremium3) AS WaiverPrem, " & _
             "Sum(tblTransaction.txnAllowance3) AS WaiverAllow, " & _
             "Sum(tblTransaction.txnPremium4) AS XtraPrem, " & _
             "Sum(tblTransaction.txnAllowance4) AS XtraAllow " & _
             "FROM tblTransaction " & _
             "WHERE (((Left([tblTransaction].[txnBillingDate], 6)) = [Forms]![frmDates]![cmbYear] & [Forms]![frmDates]![cmbMonth])) " & _
             "GROUP BY Left([tblTransaction].[Treaty],6) " & _
             "ORDER BY Left([tblTransaction].[Treaty],6), Sum(tblTransaction.txnPremium1)"
 
    Set qdf = dbs.QueryDefs(strSQL)
    qdf("[Forms]![frmDates]![cmbYear]") = [Forms]![frmDates]![cmbYear]
    qdf("[Forms]![frmDates]![cmbMonth]") = [Forms]![frmDates]![cmbMonth]
    Set rst = qdf.OpenRecordset()
 

Please note that when instead of putting the SQL into VBA, if I use an existing query, the code then works. I've taken the SQL straight from this query and pasted it into VBA, so I don't see any reason why it wouldn't work. I've written similar code to this before and had no problems.

Any help you can give me would be appreciated. Thanks.

[MMcCarthy]

You need ...

Set qbf = dbs.CreateQueryDef(strSQL)

as you are creating a new query definition.

Mary

[egewitz]

Okay. I replaced:
Set qdf = dbs.QueryDefs(strSQL)
with:
Set qdf = dbs.CreateQueryDef(strSQL)

I'm no longer getting error 3265 (Items Not Found).
I'm now getting error 3125 - followed by my SQL statement.
Am I using this method incorrectly?

[egewitz]

It looks like it was requiring me to name the querydef:
Set qdf = dbs.CreateQueryDef("",strSQL)

That part of my codes seems to be working now.

Thanks mmcarthy!!!!

[MMcCarthy]

It looks like it was requiring me to name the querydef:
Set qdf = dbs.CreateQueryDef("",strSQL)

That part of my codes seems to be working now.

Thanks mmcarthy!!!!

You're welcome.