By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

3265 - Item Not Found In This Collection

P: 6
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):
Expand|Select|Wrap|Line Numbers
  1.     Dim MyExcel As Excel.Application
  2.     Dim MyWorkbook As Excel.Workbook
  3.     Dim MyWorksheet As Excel.Worksheet
  4.     Dim strMyWorkbook As String
  5.     Dim strSQL As String
  6.     Dim dbs As Database
  7.     Dim qdf As QueryDef
  8.     Dim rst As Recordset
  9.  
  10.     ' Open the MonthlyBreakdown spreadsheet
  11.     strMyWorkbook = GetPathOrName(GetDatabasePathAndName()) & "\Copy of MonthlyBreakdown.xls"
  12.     Set MyExcel = New Excel.Application
  13.     Set MyWorkbook = MyExcel.Workbooks.Open(strMyWorkbook)
  14.     Set dbs = CurrentDb()
  15.  
  16.     strSQL = "SELECT DISTINCTROW Left([tblTransaction].[Treaty],6) AS Treaty6, " & _
  17.              "Sum(tblTransaction.txnPremium1) AS BasePrem, " & _
  18.              "Sum(tblTransaction.txnAllowance1) AS BaseAllow, " & _
  19.              "Sum(tblTransaction.txnPremium2) AS ADBPrem, " & _
  20.              "Sum(tblTransaction.txnAllowance2) AS ADBAllow, " & _
  21.              "Sum(tblTransaction.txnPremium3) AS WaiverPrem, " & _
  22.              "Sum(tblTransaction.txnAllowance3) AS WaiverAllow, " & _
  23.              "Sum(tblTransaction.txnPremium4) AS XtraPrem, " & _
  24.              "Sum(tblTransaction.txnAllowance4) AS XtraAllow " & _
  25.              "FROM tblTransaction " & _
  26.              "WHERE (((Left([tblTransaction].[txnBillingDate], 6)) = [Forms]![frmDates]![cmbYear] & [Forms]![frmDates]![cmbMonth])) " & _
  27.              "GROUP BY Left([tblTransaction].[Treaty],6) " & _
  28.              "ORDER BY Left([tblTransaction].[Treaty],6), Sum(tblTransaction.txnPremium1)"
  29.  
  30.     Set qdf = dbs.QueryDefs(strSQL)
  31.     qdf("[Forms]![frmDates]![cmbYear]") = [Forms]![frmDates]![cmbYear]
  32.     qdf("[Forms]![frmDates]![cmbMonth]") = [Forms]![frmDates]![cmbMonth]
  33.     Set rst = qdf.OpenRecordset()
  34.  
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.
Mar 13 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You need ...

Expand|Select|Wrap|Line Numbers
  1. Set qbf = dbs.CreateQueryDef(strSQL)
as you are creating a new query definition.

Mary
Mar 13 '07 #2

P: 6
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?
Mar 13 '07 #3

P: 6
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!!!!
Mar 13 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.