Run-Time Error '3265' Item not found in this collection

toididai

New member
Local time
Yesterday, 18:03
Joined
Mar 28, 2013
Messages
2
Hello,

I'm in the process of creating and exporting multiple csv files at each change in the AdvisorID. However, I keep getting the Error "3265" -Item not found in this collection at the line: CurrentDb.QueryDefs("qrySQL").SQL = strSQL

Does anyone have any idea why this happens?
******************************************************

Here are the codes:


Option Compare Database
Private Sub EmailList()
Dim rsAdvisorID As DAO.Recordset
Dim strSQL As String
Dim strFileName As String

strSQL = "SELECT DISTINCT [Advisor ID]" _
& " FROM Email_List" _
& " ORDER BY [Advisor ID];"

Set rsAdvisorID = CurrentDb.OpenRecordset(strSQL)

If Not ((rsAdvisorID.EOF) And (rsAdvisorID.BOF)) Then
rsAdvisorID.MoveFirst

Do While Not (rsAdvisorID.EOF)

strSQL = "SELECT [Advisor ID]" _
& " FROM Email_List" _
& " WHERE (((Email_List.[Advisor ID])=" & rsAdvisorID![Advisor ID] & "));"

CurrentDb.QueryDefs("qrySQL").SQL = strSQL

strFileName = "C:\Documents and Settings\My_Data\" & rsAdvisorID![Advisor ID] & ".csv"
DoCmd.TransferText acExportDelim, , "qrySQL", strFileName
rsAdvisorID.MoveNext
Loop
MsgBox "Export Complete."
End If
End Sub

******************************************************

Thank you very much.
 
The code should work (assuming qrySQL exists).

Try deleting and rebuilding the query.
 
i agree. the error is probably indicating that "qrySQL" does not exist in the collection of querydefs.

you can't use that syntax to create a new query, I would think, if that is what you are trying to do.
 
Thank you both for your quick replies.

Since I've never worked with VBA before (first time ever per my boss's request), I just have to get up to speed in a hurry and learn on the fly.

As for the "qrySQL", do I need to define it here or somewhere else in the project? An example may help.

The whole thing about this is to create a new external file (.csv) whenever there is a change of AdvisorID on the table. Currently, there
are about 500 different AdvisorID on the table.

Any help is greatly appreciated.

Thanks so much.
 
I cant look it up but it will be a different command first to create a query

CurrentDb.QueryDefs("qrySQL").SQL = strSQL


something like

dim qdf as querydef

set qdf = currentdb.createquerydef ("myquery")

and THEN

qdf.sql = "whatever"
 
Thank you both for your quick replies.

Since I've never worked with VBA before (first time ever per my boss's request), I just have to get up to speed in a hurry and learn on the fly.

As for the "qrySQL", do I need to define it here or somewhere else in the project? An example may help.

The whole thing about this is to create a new external file (.csv) whenever there is a change of AdvisorID on the table. Currently, there
are about 500 different AdvisorID on the table.

Any help is greatly appreciated.

Thanks so much.

Hello,
I am greatly puzzled by the code. The error is trivial and both Galaxiom and Dave identified the problem. But beyond that: what is that you are expecting the code to generate ? 500 single/double/triple identical entry "csv" files ? Do you realize you are comparing a recordset of the IDs back to the source of the recordset ? What is the meaning of this ? The resulting files will contain no useful information about the IDs that I can see; at best you will have one or more duplicates of the ID, (if there are multiples of ID in the Email list. Can you please shed some light on this ? Maybe I am missing something here. Thanks.

Best,
Jiri
 

Users who are viewing this thread

Back
Top Bottom