Results 1 to 2 of 2
  1. #1
    rockell333 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    33

    item not found in this collection

    I did not write this code at all. It is meant to compare addresses. We made some changes to the database however.

    Private Sub importButton_Click()
    Dim dbObj As DAO.Database, qdObj As DAO.QueryDef, sql As String, tn1 As String, tn2 As String, tn3 As String, fixed_path As String




    fixed_path = export_path & "\"

    Set dbObj = CurrentDb
    Set qdObj = dbObj.QueryDefs("qry_Tmp") it is stating object not found in this collection.. is there a query missing?

    tn1 = "current_addresses"

    sql = "DELETE * FROM [" & tn1 & "] WHERE RECORD_TYPE <> 'D';"
    dbObj.Execute sql

    sql = "ALTER TABLE [" & tn1 & "] ADD COLUMN bad_address BIT;"
    dbObj.Execute sql

    sql = "UPDATE [" & tn1 & "] SET bad_address = -1;"
    dbObj.Execute sql

    sql = "UPDATE [" & tn1 & "] SET bad_address = Switch(" & _
    "Nz(Match__cv__Mailing_Street__c, '') = '', -1," & _
    "Nz(STREET_NAME_OLD, '') = '' AND Nz(__Mailing_Street__c, '') <> '', 0," & _
    "Nz(STREET_NAME_OLD, '') <> '' AND Nz(__Mailing_Street__c, '') <> '', iif( Instr(__Mailing_Street__c, Nz(PRIMARY_NUMBER_OLD,'')) >0 AND Instr(__Mailing_Street__c, Nz(STREET_NAME_OLD,'')) >0 AND Instr(__Mailing_Street__c, Nz(SECONDARY_NUMBER_OLD,'')) >0, -1, 0));"
    dbObj.Execute sql

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tn1, fixed_path & "ereturns.xlsx"

    qdObj.Close
    Set qdObj = Nothing
    Set dbObj = Nothing
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Unless you didn't include all the code, just delete that line; you don't use the object anyway. If you do need it, the error would imply that there is no query with that name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-18-2015, 04:35 PM
  2. Item Not Found In Collection For TableDefs
    By gammaman in forum Modules
    Replies: 2
    Last Post: 06-17-2015, 07:55 AM
  3. Replies: 3
    Last Post: 11-16-2012, 10:15 AM
  4. Parent Item / Child Item Not Saving Correctly Together
    By Evilferret in forum Programming
    Replies: 6
    Last Post: 08-24-2012, 02:30 PM
  5. Item not found
    By thart21 in forum Programming
    Replies: 7
    Last Post: 04-14-2010, 10:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums