ms/access 2013 Item not found in this collectionerror 3265

I have a query that yields the above error. It is a dynamic query, where I want to locate records where some word may be found.

The error is below:
ss1

I am using MsgBox to show the text string and it looks ok:

ss2
ss3
the result database query looks alright:

ss4

My code is below and the line that crashes is below the mark XXXXX


Can anyone help?





Private Sub Fld_F42_strSearch_LostFocus()

    ' construir o big query e procurar..."
   
    Dim coreDB As DAO.Database

    Dim T00_ControlTableSet As DAO.Recordset, _
        Q61_FinalSearchSet As DAO.Recordset, _
        T26_AgregaSet As DAO.Recordset
       
    Dim Q61_FinalSearchDef As QueryDef, _
        AgregaDef As QueryDef
   
    Dim strSetSQL As String, _
        strParsed As String
       
    Dim strSearchWord As Variant
   
    Dim I As Integer

   
    Set coreDB = CurrentDb
   

    Set T00_ControlTableSet = coreDB.OpenRecordset("T00_ControlTable", dbOpenDynaset)
    Set T26_AgregaSet = coreDB.OpenRecordset("T26_Agrega", dbOpenDynaset)
     
 
    ' if query field is empty just do nothing!
   
    If Nz(Me.Fld_F42_strSearch.Text) = "" Then
        Exit Sub
    End If
   
    'find word
    strSearchWord = WordExtract(Me.Fld_F42_strSearch.Text & vbNullString)
    strParsed = "WHERE TRUE AND (("
    For I = LBound(strSearchWord) To UBound(strSearchWord)
        strParsed = strParsed & "((T26_Agrega.AgregaF02) Like '*" & strSearchWord(I) & "*')) OR "
    Next
    strParsed = strParsed & " FALSE "
    strParsed = strParsed & ");"
    MsgBox (strParsed)

   'Logar query
   
    Call LogMe("F42_strSearch \ LOSTFOCUS \ INIT\ [" & strParsef & "] \ ", _
                strCodeZone _
                & " CORE :" & strCoreID _
                & "\ USER: " & strUserID _
                & "\" & strUserName & vbCrLf _
                )
               
   
    On Error GoTo errorHandler
    ' limpar T26
    With T26_AgregaSet
        If .RecordCount > 0 Then
            .Delete
        End If
        .Close
    End With
       
    strSetSQL = "INSERT INTO T26_Agrega ( AgregaF01, AgregaF02 ) "
    strSetSQL = strSetSQL & "SELECT Q62_Agrega00.Q62_AgregaF00, Q62_Agrega00.Q62_Agrega01 "
    strSetSQL = strSetSQL & "FROM Q62_Agrega00; "
    MsgBox (strSetSQL)
    Set T26_AgregaSet = coreDB.QueryDefs("T26_Agrega")
    T26_AgregaSetDef.SQL = strSetSQL
    Set T26_AgregaSetSet = coreDB.OpenRecordset("T26_AgregaSet", dbOpenDynaset)
    T26_AgregaSet.Close

    strSetSQL = "INSERT INTO T26_Agrega ( AgregaF01, AgregaF02 ) "
    strSetSQL = strSetSQL & "SELECT Q62_Agrega01.Q62_AgregaF01, Q62_Agrega01.Q62_Agrega_F02 "
    strSetSQL = strSetSQL & "FROM Q62_Agrega01;"
    T26_AgregaSetDef.SQL = strSetSQL
   
    ' LINE THAT CRASHES:   XXXXXX
   
   
    Set T26_AgregaSetSet = coreDB.OpenRecordset("T26_AgregaSet", dbOpenDynaset)
    T26_AgregaSet.Close

    strSetSQL = "INSERT INTO T26_Agrega ( AgregaF01, AgregaF02 ) "
    strSetSQL = strSetSQL & "SELECT Q62_Agrega03.Q62_AgregaF01, Q62_Agrega03.Q62_AgregaF02 "
    strSetSQL = strSetSQL & "FROM Q62_Agrega03;"
    T26_AgregaSetDef.SQL = strSetSQL
    Set T26_AgregaSetSet = coreDB.OpenRecordset("T26_AgregaSet", dbOpenDynaset)
    T26_AgregaSet.Close

   
    Me.Fld_F42_CoreID = Forms![F91_LoggedUser]![CoreID]
    Me.Fld_F42_UserID = Forms![F91_LoggedUser]![Fld_F91_NomeCompletoUtilizador]
       
       
    strSearchWord = WordExtract(Me.Fld_F42_strSearch.Text & vbNullString)
    'MsgBox (strWordExtract)
   
    strParsed = "WHERE TRUE AND (("
    For I = LBound(strSearchWord) To UBound(strSearchWord)
        strParsed = strParsed & "((T26_Agrega.AgregaF02) Like '*" & strSearchWord(I) & "*')) "
    Next
    strParsed = strParsed & ");"
    MsgBox (strParsed)
    strSetSQL = "SELECT T26_Agrega.AgregaF02 "
    strSetSQL = strSetSQL & "FROM T26_Agrega "
    'strSetSQL = strSetSQL & "WHERE (((T26_Agrega.AgregaF02) Like '*" &
   
   
   
    '(" & strParsed & ");"
   
    'WHERE (((T26_Agrega.AgregaF01) Like "Abs")) OR (((T26_Agrega.AgregaF01) Like "xyz"));


    Set Q61_FinalSearchDef = coreDB.QueryDefs("Q61_FinalSearch")
    Q61_FinalSearchDef.SQL = strSetSQL
    Set Q61_FinalSearchSet = coreDB.OpenRecordset("Q61_FinalSearch", dbOpenDynaset)
    Me.Recalc
   
errorHandler:

   'Problema ba busca de dados
    strError = Err & ": " & Error(Err)
    MsgBox strError
    Call LogMe("F42_strSearch \ LOST FOCUS \ ERROR \ " & strError & " \ ", _
                strCodeZone _
                & " CORE :" & strCoreID _
                & "\ USER: " & strUserID _
                & "\" & strUserName & vbCrLf _
                & " \ QUERY: [" & strSetSQL & "]")
    MsgBox "Problema no acesso a dados do search geral."
    Resume Next
   

End Sub
João serras-pereiraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
The error message, 'Item not found in this collection', indicates one the fields/columns doesn't exist (can't be found).  Do both of these fields/columns exist in the db table?

SELECT Q62_Agrega01.Q62_AgregaF01, Q62_Agrega01.Q62_Agrega_F02

OM Gang
João serras-pereiraAuthor Commented:
yeah... Please check the screenshots... (actually there is a spelling error as

Q62_Agrega01.Q62_AgregaF01 should be

Q62_Agrega01.Q62_Agrega_F01

but I decided to keep coherence at this debugging stage

Screen-Shot-2015-06-04-at-12.59.33.png
Screen-Shot-2015-06-04-at-12.59.57.png
omgangIT ManagerCommented:
Try this next.
Add the line of code below.  Copy the output from the Immediate window and paste into a new query SQL statement.  We should be able to trouble shoot from the query.
Do the fields/columns specified in the INSERT INTO statement exist in the table and are spelled correctly?
OM Gang



    ' LINE THAT CRASHES:   XXXXXX
     
     Debug.Print strSetSQL       '<--- add this line

     Set T26_AgregaSetSet = coreDB.OpenRecordset("T26_AgregaSet", dbOpenDynaset)
     T26_AgregaSet.Close

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
João serras-pereiraAuthor Commented:
Ok.
It looks better. Now I am getting the below message:

ss1

I need to hold on for a few hours and will come back, after finding the Table!
João serras-pereiraAuthor Commented:
Thanks! My code was terrible!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.