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

Search Key Cannot Be Found (Error 3709)

P: 90
Access 2003; PC with Windows XP; SBS 2003

I tried searching the forums but it came up with a blank page.
I searched Google and found an answer; but the 'problem' doesn't relate to our situation!

We have a table with 24 fields; 1 AutoNumber; 1 Yes/No; 1 Memo; 2 Currency; 5 Number and the rest Text.
The AutoNumber (PK), one Text (No Duplicatess) and one of the Number fields (Duplicates OK) are indexed.

There is a form to add records to this table with a combo box 'Not In List' event to add the new text to the indexed Text field.

Occasionally (4 times so far; in maybe 2 months) the new record doesn't enter properly and when I try to delete it, I get an error message.
Expand|Select|Wrap|Line Numbers
  1. The search key was not found in any record. (Error 3709)
  2. This error occurs when an ISAM SEEK is being executed and there are no matching values in the index.
I've read that this usually means an index on a memo field; but as I showed above, no indexed memos. And at the point of adding, there is no text in the memo field at all! (That plus I have no idea what an ISAM seek is; I'm going to hunt as soon as I finish this post)

The only way I can delete the record is by compact and repairing; which in a production environment means kicking everyone out of the database (FE/BD), fixing it, and letting them back in. Not good as lost time = lost sales. Other than that it's a case of leave it till the next day (I auto compact the DB after it's been backed up) and delete it in the morning, which means that the item in question doesn't get put on our database/website until the next day. Again could be lost sales.

Not only do I get the error message, I get a load of gibberish inserted in random fields. The AutoNumber jumped from the 23000 it should be, to 877085005, one of the currency fields suddenly had a value of over 30mil etc.

Does anyone know where I can find more information about this error; or know what I can do to solve it once and for all?

Thanks
Mandi


[ Edit: ]
This is the code behind the new data event for the combo box. I didn't write it, it was there before I got here.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo38_NotInList(NewData As String, Response As Integer)
  2. Dim Db As DAO.Database
  3. Dim Rs As DAO.Recordset
  4. Dim msg, NewID As String
  5. On Error GoTo Err_CustomerID_NotInList
  6.  
  7. 'Exit this subroutine if the combo box was cleared.
  8.     If NewData = "" Then Exit Sub
  9. 'Confirm that the user wants to add the new customer.
  10.     msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
  11.     msg = msg & "Do you want to add it?"
  12.     If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
  13. 'If the user chose not to add a customer, set the Response
  14. 'argument to suppress an error message and undo changes.
  15.         Response = acDataErrContinue
  16. 'Display a customized message.
  17.         MsgBox "Please try again."
  18.     Else
  19. 'If the user chose to add a new customer, open a recordset
  20. 'using the Customers table.
  21.         Set Db = CurrentDb
  22.         Set Rs = Db.OpenRecordset("Parts", dbOpenDynaset)
  23. 'Create a new record.
  24.         Rs.AddNew
  25. 'Assign the NewID to the CustomerID field.
  26.         Rs![Part No] = NewData
  27. 'Save the record.
  28.         Rs.Update
  29. 'Set Response argument to indicate that new data is being added.
  30.         Response = acDataErrAdded
  31.     End If
  32.  
  33. Exit_CustomerID_NotInList:
  34.        Exit Sub
  35. Err_CustomerID_NotInList:
  36.        MsgBox Err.Description
  37.        Response = acDataErrContinue
  38.  End Sub
Jan 13 '09 #1
Share this Question
Share on Google+
17 Replies


DonRayner
Expert 100+
P: 489
Sounds like your DB is getting corrupted somehow. This page, Corrupt Microsoft Access MDBs FAQ has lots of information on corruption causes/fixes/prevention.
Jan 13 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
I have no concrete ideas, but I'd consider this may be a result of multiple concurrent accesses to the database.

I'm not sure exactly how your 35 users are set up, but I would look at their multi-user options (Yes. Sorry. Each PC that's used) to make sure they are all set up sensibly, and also consistently.

I agree with Don that something is causing corruptions.

PS. Technically, an AutoNumber value can only be incorrect if it is a duplicate. Otherwise it can be unexpected. No-one has ever specified what an AutoNumber value should be other than unique. This is a very commonly held misconception (although doesn't effect your problem specifically. An unexpected value conveys as much information in this case).
Jan 13 '09 #3

P: 90
Thanks for the link Don, I'll go have a look in a second.

NeoPa, I know autonumbers are just a way of the computer differentiating between the records quickly, but when I said it jumped, the record before it was 23446, the one directly after was 23448. Logic says it should have been 23447; but it was 877085005. I've never seen that happen before which was the only reason I mentioned it.
The PCs are set up identically, I don't know where you picked the 35 up from though, there's only 11 of us; two of which don't use the database. lol

As for something getting corrupted, I figured that was the case. It only ever happens to the web guy when adding products; and only ever when he's adding a new part number (the NotInList event code I posted before). But it doesn't happen everytime. He's added at least a dozen products today since the first one kicked off at us without issue.

Bloody computers lol

Thanks
Mandi
Jan 13 '09 #4

FishVal
Expert 2.5K+
P: 2,653
Code handling NotInList event does not look complete.
After record has been appended the combobox is not being required.
This could cause firing NotInList event on value existing in the table but not loaded so far to combobox control.
Jan 13 '09 #5

NeoPa
Expert Mod 15k+
P: 31,186
@mandanarchi
Just clarifying Mandi. I did say that the point was relevant in this case - just the AutoNumber concept is a much-misunderstood one.
@mandanarchi
See the previous thread I answered :D Simple as that. That question referred to having 35 users.
@mandanarchi
I won't make assumptions here, but does this mean you have a (at least 1) user accessing the database via a web interface?
Jan 13 '09 #6

P: 90
Ah, badly worded I'm afraid. By 'Web Guy', I meant the guy who makes / maintains our website. He accesses the database with a local copy of the front end on a normal PC like the rest of us.
I really need to re-read what I write before I post.
Jan 13 '09 #7

NeoPa
Expert Mod 15k+
P: 31,186
Not a problem.

Checking content prior to posting is always a good idea of course, but I suspect this wouldn't have rung any bells anyway. It already made sense from your perspective.
Jan 13 '09 #8

P: 90
@FishVal
What do you mean by combobox not being required?
As I said, I didn't write this code and while I can piece it together, I'm not particularly brilliant with recordsets.
Jan 13 '09 #9

FishVal
Expert 2.5K+
P: 2,653
@mandanarchi
Combobox list should be reloaded. For example via Requery method.

Expand|Select|Wrap|Line Numbers
  1. Me.Combo38.Requery
  2.  
Jan 13 '09 #10

P: 90
Ah fair enough.
The combo box is based on our table of parts (10k+ records), and if a new one is added, it is linked to the table of products (on which the form is based). There is no need to be able to see the entire list including the just-added record as long as the new record is there. Does that make sense?

I really think I should just give up, go to bed and try again in the morning. I can't explain anything properly when I can't even keep my eyes open.
Jan 13 '09 #11

FishVal
Expert 2.5K+
P: 2,653
@mandanarchi
Not really. :)

As long as combobox control does not reflect changes in rowsource table it will fire NotInList event which is handled in presumption the value is not in table.
Jan 13 '09 #12

P: 90
Didn't think it did.
I just tried adding
Expand|Select|Wrap|Line Numbers
  1. Me.Combo38.Requery
but it told me that I needed to save it before I could requery.
If I tried moving from the combo box it popped up the 'Not In List' message box again (Like you said), followed by the 'duplicate value in primary key' error. (So I changed the 'new value')
Then I added
Expand|Select|Wrap|Line Numbers
  1. Rs.Requery
after the Rs.Update and tested it. It worked; by that I mean added the new data, re-queried the combo box and moved to the next field.

With the original error being seemingly random, I don't know if that will stop it at all. The last time this one happened (before today) was a good few weeks ago - hence annoying.

If it happens again, I'll post back.
If anyone finds any info they think might help, please post.

Otherwise... thanks for your help all.
Mandi
Jan 13 '09 #13

NeoPa
Expert Mod 15k+
P: 31,186
I think you may be misunderstanding the issue here Mandi.

The .ReQuery should happen AFTER the item has been added to the table.

There is a potential further problem too of course. A ReQuery needs to be done on every client before checking the NotInList. In essence, this is an inappropriate design in a multi-user environment as clients don't know to ReQuery when a record is added. They have no way of knowing a record has been added until after the reQuery anyway.

A better (though still potentially flawed) way would be to issue a DLookup() call on the recordset when the operator enters a new value. In other words, don't assume that NotInList guarantees the record doesn't exist in the table.
Jan 13 '09 #14

P: 90
My post was a little fuzzy; I did add the Rs.Requery after the record is added. Line 18.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo38_NotInList(NewData As String, Response As Integer)
  2. Dim Db As DAO.Database
  3. Dim Rs As DAO.Recordset
  4. Dim msg, NewID As String
  5.  On Error GoTo Err_CustomerID_NotInList
  6.     If NewData = "" Then Exit Sub
  7.     msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
  8.     msg = msg & "Do you want to add it?"
  9.     If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
  10.          Response = acDataErrContinue
  11.          MsgBox "Please try again."
  12.     Else
  13.          Set Db = CurrentDb
  14.          Set Rs = Db.OpenRecordset("Parts", dbOpenDynaset)
  15.          Rs.AddNew
  16.          Rs![Part No] = NewData
  17.          Rs.Update
  18.          Rs.Requery
  19.          Response = acDataErrAdded
  20.      End If
  21. Exit_CustomerID_NotInList:
  22.     Exit Sub
  23. Err_CustomerID_NotInList:
  24.     MsgBox Err.Description
  25.     Response = acDataErrContinue
  26. End Sub
There are two similar forms, this one is only used to add / edit products and only one person ever adds new products so there's no need to update everyone's database really. None of the other users can access this particular form (Except me and the boss, but we don't add things)
The field it's adding is the primary key, so if the same thing does already exist (but hasn't been updated) it will kick an error up at him (and he's good enough with access to understand the errors).

I know it's probably not the best way to do it, but I didn't make the database, I'm just trying to improve it.
Jan 14 '09 #15

NeoPa
Expert Mod 15k+
P: 31,186
@mandanarchi
In that case, we are probably looking in the wrong place. It's not likely to suffer from typical multi-user issues if only one person updates it.

There is still the issue of users being unaware of remote updates until a Requery is done on their own copy, but I can'rt see that causing any great issues.

As to the cause of your corruptions, I can't see anything that might be related I'm afraid. That could be because I'm missing something, or because the relevant information is not here. Without knowing what it is likely to be then neither of us can know what it is that needs to be included to help find the answer.
Jan 14 '09 #16

P: 90
@NeoPa
I was afraid of that to be honest.
As I said, it only ever happens (or is only ever noticed..) when he is adding a new part number; but then only happens randomly.
He's added the part that kicked off yesterday this morning with no trouble.
I've tried doing exactly what he does on my own copy of the database, changing a few things to see if I can make the error occur, so far no luck.

I have no ideas what else I can look at, or even where to look. I suppose it's a case of wait and see.

Thank you both for your help anyway =)

Mandi
Jan 14 '09 #17

NeoPa
Expert Mod 15k+
P: 31,186
No worries Mandi.

I can say, that I would expect an issue such as you've described, to be timing related - involving some sort of simultaneity. It smacks of some sort of multi-user clashing, though I can't think what. If update PC sends an add request at the same time as something else is happening - particularly if either or both client setups are not quite right. I'm fishing obviously, but that's where I'd be focusing my attention if you want one last check before giving up on it.
Jan 14 '09 #18

Post your reply

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