Creating multiple queries to export to multiple excel sheets

[Integrate]

I have what may be an over complicated number of queries that I want to export to multiple sheets in excel.


This was all working fine with combo box selection queries but now the client wants to use a list box to select records to export.

I have the following code to create two of the queries based on a listbox but I keep getting the error "Item not found in this Collection" on the second query. I am guessing it is my sql that is failing me. I stepped through the code but can't figure out why it is losing it. (The select is on one line because I kept getting errors when I tried to split it so thought I would try and split out once I worked out the main issue). As you will see, I tried duplicating the Dim to create new ones but this didn't help.

Any advise is greatly appreciated. It is very messy due to my lack of knowledge.

Private Sub cmdPlanHdrExp_Click()



    On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim str1SQL As String
    Dim strWhere As String
    Dim str1Where As String
    Dim strIN As String
    Dim str1IN As String
    Dim flgSelectAll As Boolean
    Dim flg1SelectAll As Boolean
    Dim varItem As Variant
    Dim varItem1 As Variant


    Set MyDB = CurrentDb()


    strSQL = "SELECT * FROM tbl1PlanHdr"


    'Build the IN string by looping through the listbox
    For i = 0 To lstPlanHdrExp.ListCount - 1
        If lstPlanHdrExp.Selected(i) Then
            If lstPlanHdrExp.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lstPlanHdrExp.Column(0, i) & "',"
        End If
    Next i


    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [WARPL] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"


    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If


    MyDB.QueryDefs.Delete "LSMW_MAINTPLAN_HDR"
    Set qdef = MyDB.CreateQueryDef("LSMW_MAINTPLAN_HDR", strSQL)


    'Open the query, built using the IN clause to set the criteria
    str1SQL = "SELECT tbl2MaintItem.LSMKEY AS WARPL, tbl2MaintItem.WAPOS, tbl1PlanHdr.MPTYP AS I_MPTYP, tbl1PlanHdr.WSTRA AS I_WSTRA,tbl2MaintItem.PSTXT, tbl2MaintItem.TPLNR, tbl2MaintItem.EQUNR, tbl2MaintItem.BAUTL, tbl2MaintItem.MATNR,tbl2MaintItem.SERIALNR, tbl2MaintItem.DEVICEID, tbl2MaintItem.IWERK, tbl2MaintItem.WPGRP, tbl2MaintItem.AUART,tbl2MaintItem.ILART, tbl2MaintItem.GEWERK, tbl2MaintItem.WERGW, tbl2MaintItem.GSBER, tbl2MaintItem.PLNTY, tbl2MaintItem.PLNNR, tbl2MaintItem.PLNAL, tbl2MaintItem.APFKT, tbl2MaintItem.ANLZU, tbl2MaintItem.QMART, tbl2MaintItem.PRIOK,tbl2MaintItem.TASK_DETERMINE, tbl2MaintItem.KDAUF, tbl2MaintItem.KDPOS, tbl2MaintItem.BSTNR, tbl2MaintItem.BSTPO,tbl2MaintItem.SAKTO, tbl2MaintItem.PHYNR, tbl2MaintItem.ART, tbl2MaintItem.PRUEFLOS, tbl2MaintItem.STRNO " & _
             "FROM tbl1PlanHdr INNER JOIN tbl2MaintItem ON tbl1PlanHdr.WARPL = tbl2MaintItem.LSMKEY"


    'Build the IN string by looping through the listbox
    For i = 0 To lstPlanHdrExp.ListCount - 1
        If lstPlanHdrExp.Selected(i) Then
            If lstPlanHdrExp.Column(0, i) = "All" Then
                flg1SelectAll = True
            End If
            str1IN = str1IN & "'" & lstPlanHdrExp.Column(0, i) & "',"
        End If
    Next i


    'Create the WHERE string, and strip off the last comma of the IN string
    str1Where = " WHERE [LSMKEY] in " & _
               "(" & Left(str1IN, Len(str1IN) - 1) & ")"


    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flg1SelectAll Then
        str1SQL = str1SQL & strWhere
    End If


    MyDB.QueryDefs.Delete "LSMW_PLAN_ITEM"
    Set qdef = MyDB.CreateQueryDef("LSMW_PLAN_ITEM", str1SQL)


    'Clear listbox selection after running query
    For Each varItem1 In Me.lstPlanHdrExp.ItemsSelected
        Me.lstPlanHdrExp.Selected(varItem1) = False
    Next varItem1






    Exit Sub


Err_cmdOpenQuery_Click:


    If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
    End If




End Sub

The next issue I have is working out how to work this sub into my export sub (if possible) which is below:

Public Sub ExpPlanHdr_AfterUpdate()Dim strSaveAsFilter As String
Dim strSaveAsFileName As String


  On Error GoTo PROC_ERR


strFilter = ahtAddFilterItem(strSaveAsFilter, "Excel Files (*.xlsx)", "*.xlsx")
strSaveAsFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strSaveAsFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)






DoCmd.TransferSpreadsheet acExport, , "LSMW_MAINTPLAN_HDR", _
strSaveAsFileName
DoCmd.TransferSpreadsheet acExport, , "LSMW_PLAN_ITEM", _
strSaveAsFileName


PROC_EXIT:
  Exit Sub


PROC_ERR:
  Resume PROC_EXIT
End Sub

[CJ_London]

what does this mean?

"Item not found in this Collection" on the second query

what is the second query? what line of code is generating the error?

[ItsMe]

It may be that one of your WHERE clauses is trying to add criteria to a field that does not exist or is misspelled. More likely, you have a typo in your SQL string. After initializing str1SQL, I would add a line of code to print str1SQL to the immediate window. Then, check the result for typos. Most common are probably line returns that get autocorrected while pasting.

Debug.Print str1SQL

[Integrate]

Thanks for the help. I found the issue, the query had been deleted in a previous run and so couldn't find it to delete, hence the error.