Search Key Error 3709 - On 2 fields in the table - All other fields allow update!

[maxis]

I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.



The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.

I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.

I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.

By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. HELP - it's been a long day and I think I'm tired. Do I need to update the indexes somehow when the users selects a new empid or status code?

Any hints? Have you seen this before?
Thanks.

[RuralGuy]

A Compact and Repair recreates all of the indexes in the db, if that helps.

[maxis]

That's why we're trying to fix it. Twice a week we get this error and do the compact and repair. Initially thought we were doing a me.refresh in the wrong place until we were able to duplicate it over and over and the records really aren't corrupted. The index is getting out of sync somehow. Trying to minimize database maintenance and down time; always these two fields. I think its the 50 character text field. Not even sure why we need it indexed.

[RuralGuy]

If you left the indexes for these two fields out of the system then I believe the only down side is a search on these two fields would be slower. It might be worth trying for a while to see if the users complain or even need these indexes. Just a thought. Just guessing here. I think it is great that you were able to narrow it down to just these two fields and their indexes.

[maxis]

I think you may be right RG. Worth a try isn't it? But in 20 years I've never fixed a problem like this (run-time) by deleting an index on a field. I keep looking for some form mechanics that isn't working or not tied together properly between the main and sub form, but can't see anything unusual. It's not even a NEW record, the fields have a default value in them to begin with, so it's just updating the fields which is pretty straight forward. It's one of those things you know you're putting on a band-aid but haven't really solved the problem. I don't normally index a 50 character text field so I'm pretty comfortable with trying a test, but it bugs me that an index would fail like this when duplicates are OKAY. There's only about 50000 rows in the table so it's not that big of table. Well, thank YOU for two quick responses. Much appreciated.

[RuralGuy]

It seemed like it was worth a try and I'm not a big fan of indexing large text fields anyway. I think you have a handle on the issue at any rate. Good luck.

[maxis]

thank you... always good to have a second set of eyes... time will tell... change went into production tonight... thanks again