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

[toididai]

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]

The code should work (assuming qrySQL exists).

Try deleting and rebuilding the query.

 

[gemma-the-husky]

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]

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]

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]

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