Solved

"Search Key Not Found" Error when running macro

Posted on 2008-06-25
12
466 Views
Last Modified: 2013-11-27
jimpen helped me with some coding some weeks (or months) ago, but I am still having trouble making it work.

I get an "Run Time Error '3709': The Search Key was Not Found in Any Record "error message when running the sub NewParetoGen()

Can anyone tell me what is going wrong?

This is supposed to transpose a query into a table. I have attached it below.
Option Compare Database
 
Public Sub NewParetoGen()
 
'Delete Existing Tables
 
'Declare strSource As String, strTarget As String
    Dim strSource As String
    Dim strTarget As String
 
'set them equal to the intended arguments
    strSource = "FifteenthStWoodqryFPYPareto"
    strTarget = "FifteenthStWoodFPYParetoTable"
 
    'If CheckTableExists(strTarget) = True Then
        'DoCmd.DeleteObject acTable, strTarget
    'End If
 
    Call NewTransposer(strSource, strTarget)
 
End Sub
 
Function NewTransposer(strSource As String, strTarget As String)
 
   Dim db As DAO.Database
   Dim tdfNewDef As DAO.TableDef
   Dim fldNewField As DAO.Field
   Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
   Dim i As Integer, j As Integer
 
   'On Error GoTo Transposer_Err
 
   Set db = CurrentDb()
   Set rstSource = db.OpenRecordset(strSource)
   rstSource.MoveLast
 
   ' Create a new table to hold the transposed data.
   ' Create a field for each record in the original table.
   Set tdfNewDef = db.CreateTableDef(strTarget)
   For i = 0 To rstSource.RecordCount
      Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
      tdfNewDef.Fields.Append fldNewField
   Next i
   db.TableDefs.Append tdfNewDef
   tdfNewDef.Fields(0).Name = "Defect"               ' <-- added
   tdfNewDef.Fields(1).Name = "QTY"
 
If DoesTblExist(strTarget) = True Then
     DoCmd.SetWarnings False
     DoCmd.DeleteObject acTable, strTarget
     DoCmd.SetWarnings True
End If
 
DoCmd.CopyObject , strTarget, acTable, strSource
 
End Function
 
'-----------------------------------------------------------
'Added function:
'-----------------------------------------------------------
Public Function DoesTblExist(strTblName As String) As Boolean
    On Error Resume Next
    Dim db As Database, tbl As TableDef
    Set db = CurrentDb
    Set tbl = db.TableDefs(strTblName)
    If Err.Number = 3265 Then   ' Item not found.
       DoesTblExist = False
       Exit Function
    End If
    DoesTblExist = True
End Function

Open in new window

0
Comment
Question by:Rex85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 21870399
Guaranteed record corruption ... run this procedure.

Try a Compact and Repair.  If that fails, try this:

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 

Author Comment

by:Rex85
ID: 21870417
Does "Guaranteed record corruption" mean that I have record corruption, or that this NewParetoGen sub is a guarantee that I will corrupt them?
0
 
LVL 75
ID: 21870449
Sorry ...

It (99% if the time) means that one or more fields in one or more records are corrupt.  Probably just one field in one record.  I see this a LOT.

mx
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Rex85
ID: 21874405
I tried the suggestions you had with no effect.

One thing I noticed, in the last line...

DoCmd.CopyObject , strTarget, acTable, strSource

of the sub, strSource is a Query, not a table. I changed acTable in that line to acQuery, and now I don't get the "Search key not found" error. It still doesn't work, but I don't get that error. I either get nothing, as in executes, but has no visible output, or I get a "table already exists" error at the line

db.TableDefs.Append tdfNewDef

0
 
LVL 75
ID: 21875919
Can you zip up the MDB, then attach the file for upload here  ... removing any sensitive data of course?  

Give specific info as to how to reproduce the problem, which table, etc.

mx
0
 

Author Comment

by:Rex85
ID: 21877392
OK. The whole thing is about 50MB, so I'll have to pull out just the part being dealt with here. Thanks.

Rex
0
 
LVL 75
ID: 21877428
Be sure to Compact & Repair, then Zip up.  Post info here how to reproduce the issue - be explicit.

thx.mx
0
 

Author Comment

by:Rex85
ID: 21877786
Running the sub

Public Sub NewParetoGen()

in the jimpen Module generates the attached error and the debug higlights the line as in the attached file. It always happens.

The query involded here is querying a union query which combines two production lines into a plant. (the Wood plant) It then creates a sum of each of the various defect types for a given period of time for that plant.

The problem is that the query is one row with columns. I need the data in two columns with multiple rows in order for it to feed Minitab, which makes the Pareto charts. Hence the attempt at a Transposing procedure.

I don't have a zipper, so I made a back-up and then stripped out everything except the files in question for this problem. It made it pretty small.
KO-Quality-Master-2008-Linked-20.mdb
Error-Generated-6-26-2008.bmp
debug-line-6-26-2008.bmp
0
 
LVL 75
ID: 21877841
but when and how do you get this error:

" "Run Time Error '3709': The Search Key was Not Found in Any Record"

mx
0
 

Author Comment

by:Rex85
ID: 21877993
I can't recreate that error now. It only gives me the Table already exists error. The only thing I did was change that acTable to acQuery and run the NewParetoGen sub. Since then, I only get the table error, not the index error. I changes the acQuery back to acTable, but still, only the Table error.
0
 

Author Closing Comment

by:Rex85
ID: 31470793
This was a while ago. This didn't realy fix the problem, but I believe it pointed me in the right direction. Thanks.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question