Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    ERROR: The search key was not found in any record

    Hi so I inherited this database and while I'm fairly familiar with Access, this is my first experience with VBA. I've got a forum that is supposed to import a CSV file and dump the data into a table. Below is the code. However, after I select the file, it gives me the error above. Any advice? I've tripled checked the headers in the CSV file match the headers in the table. I've rebuilt the database from scratch to ensure it isn't corrupted. I've looked online for tips but nothing it working.



    Code:
    Option Compare DatabasePublic Sub bttnProcessIt_Click()
    
    
    Dim wdShell As Object
    
    
    On Error GoTo ImportIt_Err
    
    
        MsgBox "Remember to save the report file as DATA.csv in the Audits folder", vbOKOnly
        
    
    
        ' Prompt user for file path for the Raw spreadsheet
        Application.FileDialog(msoFileDialogOpen).Title = "Please select the audit file for processing"
        Application.FileDialog(msoFileDialogOpen).InitialFileName = "F:\Audits"
        Application.FileDialog(msoFileDialogOpen).Filters.Add "Text Files", "*.csv", 1
        Application.FileDialog(msoFileDialogOpen).FilterIndex = 1
        Application.FileDialog(msoFileDialogOpen).Show
        strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
                
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qryClear_RawAudit"
        DoCmd.RunSavedImportExport "Import-Audit"
        DoCmd.RunSavedImportExport "Export-Audit file"
    
    
        StrResponse = MsgBox("Process Complete!  Do you want to open the resulting spreadsheet?", vbYesNo)
        If StrResponse = 6 Then
         Set wsShell = CreateObject("WScript.Shell")
         wsShell.Run Chr(34) & "F:\Audits\Findingsx" & Chr(34), 1, False
        End If
    
    
    ImportIt_Exit:
        Exit Sub
    
    
    ImportIt_Err:
        MsgBox Error$
        Resume ImportIt_Exit
    
    
    End Sub

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well it can only be from one of those queries?
    Walk through the code with F8 after setting a breakpoint on the SetWarnings line and post the SQL for the one that generates the error?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Also to narrow it down you could temporarily comment out the "On Error Go To ..." line and the debugger would stop at the error line.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Usually that is a sign of data corruption. You say you rebuilt the database from scratch, did you also run the Compact and Repair?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Post the SQL from what? The form or the queries? I'm a bit of a noob

    And yes I did do a compact and repair. No luck

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    The form will not have any SQL, the queries will.?
    I am expecting it to error on one of those lines that run a query.
    Then open that query up and post the SQL here.

    Vlad believes the DB is corrupt, so I'd go with his thinking first. I am just posting the approach I would take if it happened to me.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    I compacted and repaired it and still no luck. Giving the same error message.

    I believe qryClear_RawAudit is the only query cited in the VBA. Here's its SQL:

    Code:
    DELETE RawAudit.ID, RawAudit.username, RawAudit.[user first name], RawAudit.[user last name], RawAudit.customerid, RawAudit.[customer name], RawAudit.[customer firstname], RawAudit.[customer lastname], RawAudit.[current department], RawAudit.time, RawAudit.action, RawAudit.resultFROM RawAudit;

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Some other things to look at:
    https://stackoverflow.com/questions/...in-access-2010
    Can you open the RawAudit table and try to visually inspect it for any records that display Deleted#? If you see any delete those corrupted records and try again your code.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by templeowls View Post
    I compacted and repaired it and still no luck. Giving the same error message.

    I believe qryClear_RawAudit is the only query cited in the VBA. Here's its SQL:

    Code:
    DELETE RawAudit.ID, RawAudit.username, RawAudit.[user first name], RawAudit.[user last name], RawAudit.customerid, RawAudit.[customer name], RawAudit.[customer firstname], RawAudit.[customer lastname], RawAudit.[current department], RawAudit.time, RawAudit.action, RawAudit.resultFROM RawAudit;
    Did you copy and paste that?, as there is no space before the FROM?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Yes I copied and pasted it

  11. #11
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    I guess I don't even understand what the "search key" is...

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Put a space between result and FROM

    Then try it.?

    Access will believe you are trying to retrieve a field called RawAudit.resultFROM I suspect, then there is the RawAudit word on it's own?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    I tried it. Same issue

  14. #14
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    We still haven't pinned down the error line. Post #3 should allow the code to stop with the error line highlighted.

  15. #15
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Could you input the error comment into the code I've supplied? I'm honestly not sure how to input that

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-10-2022, 07:10 PM
  2. Replies: 1
    Last Post: 07-24-2015, 11:31 AM
  3. The search key was not found in any record
    By virtualprg in forum Import/Export Data
    Replies: 46
    Last Post: 08-26-2014, 10:51 AM
  4. Search key was not found in any record.
    By mgio in forum Access
    Replies: 2
    Last Post: 08-05-2014, 01:32 PM
  5. Replies: 2
    Last Post: 11-12-2013, 07:06 PM

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