Search Key Not Found Error

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Search Key Not Found Error

Post by Leesha »

Hi,
I am getting an error that states "The Search Key was not found in any record." Basically the code pulls in data from an Excel Spreadsheet. If the table "IMPORT" is not present in the db, it is created with the info being imported and if it is there it simply imports the data in the table. That code is running fine.

My issue is that this code has been running fine for months and now all of a sudden I'm getting the above error, as is everyone else who is using the database. The only thing that has changed is that the file format that is being imported was modified but that should not be an issue since table "IMPORT" is being deleted and restored.

I've included a link to a stripped down version since I kept getting an error that says the file was too big.
https://www.dropbox.com/s/gypl5grnwdmds ... r.zip?dl=0

I've never gotten this error before so am not sure what to do with it. I'm "assuming" it means that something in Dlookup is missing however everything is the same as its been for months.

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search Key Not Found Error

Post by HansV »

The headers/field names in E1, R1 and U1 begin with a space. That is not allowed in Access. Remove the leading space from those cells. You should then be able to import the worksheet.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search Key Not Found Error

Post by HansV »

You can shorten the code to

Code: Select all

Private Sub cmdOpenForm_Click()
    'checks to be sure import table exists and wasn't deleted by accident
    'Looks to see if import table exisits
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='Import'")) Then
        'Table Exists so it will be deleted so new info can be imported
        DoCmd.DeleteObject acTable, "Import"
    End If
    'Imports new data and recreates the table
    DoCmd.TransferSpreadsheet TableName:="IMPORT", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.xlsx", HasFieldNames:=True
End Sub
There is no need to have the same DoCmd.TransferSpreadsheet in both the If and Else parts.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Search Key Not Found Error

Post by Leesha »

Thank you so much Hans! I wish the error had been more clear so I could figure out the issue. I appreciate you and your brain!
Leesha