By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Search Key Cannot Be Found

P: 90
I've hunted for info about this and come up with loads.
Jet 4.0 error - fixed in latest service pack. We all have latest service pack.
Copy faulty table, delete old, remove fault field etc

This error has happened twice now, both seemingly caused by the same thing, but I can't pinpoint it.

Two tables - parts and products. Parts contains generic info about stuff we sell (part no, supplier part no etc), products contains the specific info (model, manufacturer, description) [I did not set this up myself, I'm just trying to fix it]

One of the guys adds new products and had to manually add the supplier part numbers by hand. I created a few queries to add them based on the part number and suppliers product list.
Expand|Select|Wrap|Line Numbers
  1. UPDATE Parts 
  2. LEFT JOIN supplier ON Parts.[Part No] = supplier.manuf_part_no SET Parts.supplier_Part = supplier!supplier_part_no
  3. WHERE (((Parts.supplier_Part) Is Null) AND ((Parts.PartID)=[Forms]![Edit Product]![PartID]) AND ((supplier.supplier_part_no) Is Not Null));
That's the SQL behind my query.
Part No = manufacturers part number
supplier_part/_no = suppliers related part number
PartID = primary key in parts table

This button that I made him has 11 of those queries (one for each supplier) and updates the relevant fields so he doesn't need to come out and go back into the record.
Mostly it works fine; but twice it has removed all information, replaced parts with the square 'unknown character' thing, removed the part number and not let me add it to another record (duplicate key) and not let me change it in the table (search key cannot be found).

If I copy the table, I can delete it, but I can't delete the old table (or the fields) due to related records in Products. If I compact and repair (after kicking everyone out - it's a FE/BE), then it lets me edit the field (or delete it if there's no related record in the products table).

Can anyone suggest what may be causing this?
Like I said, it's only happened twice, both times when the queries have been run; but the queries have been run hundreds of times without issue.

It's not really a big deal, until it's a popular product that kicks it, and I can't keep asking everyone to come out of the database so I can fix a product.

Thanks for any help in advance.

If you think you need more info, ask =)
Dec 4 '08 #1
Share this Question
Share on Google+
1 Reply

P: 90
Oh, just spotted that 'index on memo field' can also cause this issue.
There are no memo fields in the Parts table.
There is one in the Products table, but that is rarely used and is not indexed.
Dec 4 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.