Run-Time Error '3265' Item not found in this collection (1 Viewer)

toididai

New member
Local time
Today, 09:06
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:06
Joined
Jan 20, 2009
Messages
11,932
The code should work (assuming qrySQL exists).

Try deleting and rebuilding the query.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Sep 12, 2006
Messages
13,892
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.
 

toididai

New member
Local time
Today, 09:06
Joined
Mar 28, 2013
Messages
2
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.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Sep 12, 2006
Messages
13,892
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"
 

Solo712

Registered User.
Local time
Today, 12:06
Joined
Oct 19, 2012
Messages
756
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 (Users: 0, Guests: 1)

Top Bottom