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.
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.