Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Unanswered: Error 3709 Search Key was not found

    I'm getting the Error 3709 "search key was not found" message suddenly.

    This code, shown below is sql behind a "make table" query which runs when the application first opens to copy certain fields from a much larger table to a smaller worktable so that the work table is completely up to date. The code works perfectly in this situation.

    We also have a button to run the query manually when the master table has been updated. When we run the query manually, we get the error.

    PHP Code:
    SELECT LineItem.ItemIDLineItem.ItemDescriptionLineItem.ItemClassLineItem.ItemIsInactiveLineItem.SalesDescriptionLineItem.PurchaseDescriptionLineItem.CustomField1LineItem.CustomField2LineItem.CustomField3LineItem.CustomField4LineItem.CustomField5LineItem.WeightLineItem.LaborCostLineItem.OrderQty INTO tblLineItem
    FROM LineItem
    WHERE 
    (((LineItem.ItemClass)=Or (LineItem.ItemClass)=3) AND ((LineItem.ItemIsInactive)<>1)); 
    Currently, to update the worktable, we have to exit the database and re-open which runs the query without an issue.

    We've used this query for years - just began doing this a few weeks ago.

    Thanks in advance.
    Matt Wilcox
    Casters & Wheels at www.apollocaster.com
    www.apollocaster.com/store

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    search term sounds liek its going to be a problem with the where clause
    WHERE LineItem.ItemClass in (1, 3) AND LineItem.ItemIsInactive)<>1;

    LineItem.ItemIsInactive)<>1 looks odd to me, is that a numeric or a boolean value, if boolean then you should use either true/false or vbtrue/vbfalse

    HOWEVER it could be a corruption issue, and one that may not be fixed by comapct and repair
    Access Help and How-to - Microsoft Office Error 3709 "The Search Key was not found in any record" - UtterAccess Discussion Forums
    sugegsts its a bad data issue
    Access Help and How-to - Microsoft Office Error 3709 "The Search Key was not found in any record" - UtterAccess Discussion Forums
    suggests:-
    I FIXED this problem which effected one field in one record only on my database. Could not delete it or change the amount. So I EXPORTED the table to a dummy database. I then renamed the bad table in my db (which I later deleted)
    The export obviously got rid of the problem, because I was able to delete the bad record in the dummy database and then EXPORT it back to my database.

    suggests
    RESOLUTION
    If you must enter a large amount of text in the Memo field, delete the index for the Memo field. To do so follow these steps: 1. Open the table with the Memo field in Design view.
    2. On the View Menu, click Indexes.
    3. Click the index for the Memo field, and then delete it.

    are you using memo columns?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Smile

    Thank you healdem for the expert and thorough reply!!

    I leaned away from the "WHERE" clause issue or the Corruption possibility on the basis that the query worked fine on initial db startup. It just wouldn't work when we manually triggered it later. Corruption or the WHERE clause should be a consistent error.

    However, your coments were on target and got me thinking about how to make the query and copy process more efficient. I made a few tweaks to the query and how it functions with both the local and remote dbs. Can't say exactly what fixed it, but the error is now gone and the query working as needed.

    Thank you again for the thoughtful response.
    Matt Wilcox
    Casters & Wheels at www.apollocaster.com
    www.apollocaster.com/store

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •