Search Key Cannot Be Found (Error 3709)

[mandanarchi]

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.

The search key was not found in any record. (Error 3709)
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.

Private Sub Combo38_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim msg, NewID As String
On Error GoTo Err_CustomerID_NotInList

'Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
'Confirm that the user wants to add the new customer.
    msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    msg = msg & "Do you want to add it?"
    If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add a customer, set the Response
'argument to suppress an error message and undo changes.
        Response = acDataErrContinue
'Display a customized message.
        MsgBox "Please try again."
    Else
'If the user chose to add a new customer, open a recordset
'using the Customers table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Parts", dbOpenDynaset)
'Create a new record.
        Rs.AddNew
'Assign the NewID to the CustomerID field.
        Rs![Part No] = NewData
'Save the record.
        Rs.Update
'Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
    End If

Exit_CustomerID_NotInList:
       Exit Sub
Err_CustomerID_NotInList:
       MsgBox Err.Description
       Response = acDataErrContinue
 End Sub

[DonRayner]

Sounds like your DB is getting corrupted somehow. This page, Corrupt Microsoft Access MDBs FAQ has lots of information on corruption causes/fixes/prevention.

[NeoPa]

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).

[mandanarchi]

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

[FishVal]

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.

[NeoPa]

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.

Just clarifying Mandi. I did say that the point was relevant in this case - just the AutoNumber concept is a much-misunderstood one.

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

See the previous thread I answered :D Simple as that. That question referred to having 35 users.

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.

I won't make assumptions here, but does this mean you have a (at least 1) user accessing the database via a web interface?

[mandanarchi]

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.

[NeoPa]

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.

[mandanarchi]

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.

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.

[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.

Combobox list should be reloaded. For example via Requery method.

Me.Combo38.Requery

[mandanarchi]

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.

[FishVal]

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?

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.

[mandanarchi]

Didn't think it did.
I just tried adding

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

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

[NeoPa]

I think you may be misunderstandin g 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.

[mandanarchi]

My post was a little fuzzy; I did add the Rs.Requery after the record is added. Line 18.

Private Sub Combo38_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim msg, NewID As String
 On Error GoTo Err_CustomerID_NotInList
    If NewData = "" Then Exit Sub
    msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    msg = msg & "Do you want to add it?"
    If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
         Response = acDataErrContinue
         MsgBox "Please try again."
    Else
         Set Db = CurrentDb
         Set Rs = Db.OpenRecordset("Parts", dbOpenDynaset)
         Rs.AddNew
         Rs![Part No] = NewData
         Rs.Update
         Rs.Requery
         Response = acDataErrAdded
     End If
Exit_CustomerID_NotInList:
    Exit Sub
Err_CustomerID_NotInList:
    MsgBox Err.Description
    Response = acDataErrContinue
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.