asked on

QueryDefs throwing error in ACCESS 2003

I am trying to export data from a form to Excel and trying to get the attached code to work. For some reason it fails, but it looks ok so any suggestions?

Private Sub cmdExport_Click()
Dim strExport As String
Dim strExportQuery As String
Dim strAssociate As String
Dim strWhere As String
Dim strFile As String
strAssociate = Me.cmb_filter_by_user.Column(0)

If strAssociate = "<All>" Then
    strWhere = "WHERE t_chk_job.active_flag  = 'Y'"
Else
    strWhere = "WHERE t_chk_job.active_flag  = 'Y' AND t_chk_job.first_associate_id = '" & strAssociate & "' "
End If

strExport = "SELECT t_chk_report.crs_job_id, t_chk_job.first_associate_id AS job_first_associate_id, " & _
    "t_chk_job.crs_job_code, t_chk_job.active_flag AS active_job_flag, t_chk_report.crs_report_id, " & _
    "t_chk_report.crs_report_nm, t_chk_report.crs_report_type_code, t_chk_report.crs_report_subtype_code, " & _
    "Trim([crs_appl_rpt_id]) AS crs_appl_report_id, t_chk_report.report_data_category, t_chk_report.report_create_minutes, " & _
    "t_chk_report.report_review_minutes, t_chk_report.related_entity_id, t_chk_report.related_entity_type_cd, " & _
    "t_chk_report.internal_recipient_flag, t_chk_report.bespoke_flag, t_chk_report.report_origin_code, t_chk_report.primary_cro " & _
    "FROM t_chk_job INNER JOIN t_chk_report ON t_chk_job.crs_job_id = t_chk_report.crs_job_id " & _
    " " & strWhere & " " & _
    "ORDER BY t_chk_job.first_associate_id, t_chk_job.crs_job_code "
Debug.Print strExport

    CurrentDb.QueryDefs(strExportQuery).SQL = strExport  'ERROR 3265 ITEM NOT FOUND IN THIS COLLECTION?

   strFile = "C:\Documents and Settings\sasmith\My Documents\MyExport.xls"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
       strExportQuery, strFile

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sandra Smith

ASKER

Capricorn1, i think this is where I am getting confused on using this methodolgy. Isn't strExportQuery being defined when it is assigned to strExport? Then using strExportQuery in the TransferSpreadsheet method not correct?

Sandra Smith

ASKER

Based on your comment, I got to thinking that perhaps the code that I copied to do this is not correct. I changed it to the attached and it seems to be working now.

Private Sub cmdExport_Click()
Dim strExport As String
Dim qdfExportQuery As QueryDef
Dim qryExport As String
Dim strAssociate As String
Dim strWhere As String
Dim strFile As String
strAssociate = Me.cmb_filter_by_user.Column(0)
strFile = "C:\Documents and Settings\sasmith\My Documents\MyExport.xls"

If strAssociate = "<All>" Then
    strWhere = "WHERE t_chk_job.active_flag  = 'Y'"
Else
    strWhere = "WHERE t_chk_job.active_flag  = 'Y' AND t_chk_job.first_associate_id = '" & strAssociate & "' "
End If

strExport = "SELECT t_chk_report.crs_job_id, t_chk_job.first_associate_id AS job_first_associate_id, " & _
    "t_chk_job.crs_job_code, t_chk_job.active_flag AS active_job_flag, t_chk_report.crs_report_id, " & _
    "t_chk_report.crs_report_nm, t_chk_report.crs_report_type_code, t_chk_report.crs_report_subtype_code, " & _
    "Trim([crs_appl_rpt_id]) AS crs_appl_report_id, t_chk_report.report_data_category, t_chk_report.report_create_minutes, " & _
    "t_chk_report.report_review_minutes, t_chk_report.related_entity_id, t_chk_report.related_entity_type_cd, " & _
    "t_chk_report.internal_recipient_flag, t_chk_report.bespoke_flag, t_chk_report.report_origin_code, t_chk_report.primary_cro " & _
    "FROM t_chk_job INNER JOIN t_chk_report ON t_chk_job.crs_job_id = t_chk_report.crs_job_id " & _
    " " & strWhere & " " & _
    "ORDER BY t_chk_job.first_associate_id, t_chk_job.crs_job_code "

Set qdfExportQuery = CurrentDb.CreateQueryDef("qryExport", strExport)
   
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport", strFile

DoCmd.DeleteObject acQuery, "qryExport"

End Sub

Open in new window

Sandra Smith

ASKER

Yes, I see now that the query really was not being defnied properly.