We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


Receiving the following Error 'The Search Key was not found in any record' What is causing the error?

Imoutwest asked
Medium Priority
Last Modified: 2008-01-09
There is probably a simple solution, but this has happened three times now, corrupts the record that is active, then I receive the error when I try to edit or delete the record. (Lots of Squares pasted in the record).

After doing a Compact/Repair I can delete the record, but the data is lost.

Basically, the error occurs when the Users runs an SQL string in VBA that updates specific / open records. The user has made a change to the current record, but the record is not updated (As it is still the active record), then the User click on the update button.

Any suggestion on correcting the code or trapping the error and preventing the update?
Watch Question

How do you fetch the record?
Is this an unbound or bound form?

Having a record on a form that's also updated by a query triggered on the form is asking for trouble, why do you need this in the first place?
Can you elaborate on the functional need ?

Top Expert 2006

Now, if this form is bounded, are u updating the same table or a different one. If the same, could u not set bounded fields then issue something like Me.Dirty = False to force the save?

what version Access u using?
Its not quite what u have http://support.microsoft.com/default.aspx?scid=kb;EN-US;q301474
but it suggests that perhaps u should check for latest updates etc

Another thing, if u keep getting this then there may be corruption elsewhere. A compact/repair should help it,. which as u say u are doing. So I dont think decompile may add some help.


Thanks for the quick reply - I just replicated the error - 'Write Conflict', I assume because the data in the record changed and wasn't updated before the query.

This is what I'm doing, open to a better method!

I using a form (continious) that allows the user to review specific records in the database.

The only field being changed on the Record is an option button optVerifyAll.

The User verifies that all of the data is correct and then can either click Verify on each record or Click on a 'Verify All' button to verify all of the specific records. For the verify all to work the User must specific Study / Cohort / Period / Dose Day / Treatment. If any of these field are left blank on the filter form that opens the Record (continous) form then the user can't update the open records.

The reason that I do it this way is so the user can see that once the records have been reviewed, that they are now verified. (the form requeries after running the update).  

Clear as the Mississippi? Would it help to post the code? Although I know what is causing the error, how should I proceed...
>>Would it help to post the code?
It would


Okay, it gets a little busy, hope I don't cause eye-strain!

********** Code in a OnClick Event for a Command Button cmdVerifyAll

        ' Verify Dose Grid Data with Initials of Verifier, then Lock fields
    Dim strTitle As String, strMsg As String
    Dim Style, Title, Response
    Dim boShowInfo As Long ' To determine if user has selected not to see warnings
    Dim ListControl As Integer ' Determine number of Treatments

    ListControl = Forms!frmfilter.Filter5.ListCount
    Title = "So, You Want This To Be Easy!"
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    strMsg = "Note - " & vbCrLf & vbCrLf & _
        "Do You want to Verify All of the Open Records?" & vbCrLf & vbCrLf & _
        "You must have the following Information Selected:" & vbCrLf & vbCrLf & _
        Space$(4) & "NWK Study" & vbCrLf & _
        Space$(4) & "Cohort" & vbCrLf & _
        Space$(4) & "Period" & vbCrLf & _
        Space$(4) & "Dose Day" & vbCrLf & vbCrLf & _
        Space$(4) & "Treatment is Optional" & vbCrLf & vbCrLf & _
        "Green Dragon!"
    strTitle = "Verifiers' Initials"
    boShowInfo = DLookup("[ShowInfo]", "tblShowInfo", "[RecordID] = 1")
        ' Check if first entry upon form opening
    If boShowInfo = 0 Then
        DoCmd.SetWarnings True
        Response = MsgBox(strMsg, Style, Title)
        DoCmd.SetWarnings False
        Response = vbYes
    End If

    If Response = vbYes Then
        Call fncUserEdits(Forms!frmPassword.txtUserID, Me.Name, Me.txtRecID, Me.txtNWK, Me.ActiveControl.Name)
        txtNewInitials = Left(InputBox("Enter Verifiers Initials", strTitle), 3)
        Dim strSQL As String, strNWK As String
        Dim strCohort As String, strPeriod As String
        Dim strDoseDay As String, strTreatment As String
        strNWK = Forms!frmfilter.Filter1
        strCohort = IIf(Nz(Forms!frmfilter.Filter2.Value) = vbNullString, Null, Forms!frmfilter.Filter2) 'Forms!frmfilter.Filter2
        strPeriod = IIf(Nz(Forms!frmfilter.Filter3.Value) = vbNullString, Null, Forms!frmfilter.Filter3) 'Forms!frmfilter.Filter3
        strDoseDay = IIf(Nz(Forms!frmfilter.Filter4.Value) = vbNullString, Null, Forms!frmfilter.Filter4) 'Forms!frmfilter.Filter4
        strTreatment = IIf(Nz(Forms!frmfilter.Filter5.Value) = vbNullString, "", Forms!frmfilter.Filter5)
            ' Only one treatment or treatment is selected
        If ListControl <= 1 Then
            strSQL = "UPDATE tblDispensingPage SET tblDispensingPage.Verified = -1, tblDispensingPage.VerInitials =" & Chr(34) & txtNewInitials & Chr(34) & _
                " WHERE (((tblDispensingPage.NWK)=" & Chr(34) & strNWK & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Cohort)=" & Chr(34) & strCohort & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Period)=" & Chr(34) & strPeriod & Chr(34) & ")" & _
                " AND ((tblDispensingPage.DoseDay)=" & Chr(34) & strDoseDay & Chr(34) & "));"
        ElseIf strTreatment = "<All>" Then
            strSQL = "UPDATE tblDispensingPage SET tblDispensingPage.Verified = -1, tblDispensingPage.VerInitials =" & Chr(34) & txtNewInitials & Chr(34) & _
                " WHERE (((tblDispensingPage.NWK)=" & Chr(34) & strNWK & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Cohort)=" & Chr(34) & strCohort & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Period)=" & Chr(34) & strPeriod & Chr(34) & ")" & _
                " AND ((tblDispensingPage.DoseDay)=" & Chr(34) & strDoseDay & Chr(34) & "));"

        ElseIf strTreatment <> "" Or IsNull(strTreatment) Then
            strSQL = "UPDATE tblDispensingPage SET tblDispensingPage.Verified = -1, tblDispensingPage.VerInitials =" & Chr(34) & txtNewInitials & Chr(34) & _
                " WHERE (((tblDispensingPage.NWK)=" & Chr(34) & strNWK & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Cohort)=" & Chr(34) & strCohort & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Period)=" & Chr(34) & strPeriod & Chr(34) & ")" & _
                " AND ((tblDispensingPage.DoseDay)=" & Chr(34) & strDoseDay & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Treatment)=" & Chr(34) & strTreatment & Chr(34) & "));"
            ' Warn User that all Records will be Verified
        ElseIf ListControl > 1 And strTreatment = "" Or IsNull(strTreatment) Then
            Title = "Did not Select a Treatment"
            strMsg = "You didn't Select a Treatment; so All Labels (Records)" & vbCrLf & _
                    "that match the Study Number, Cohort, Period and " & vbCrLf & _
                    "Dose Day will be Verified. " & vbCrLf & vbCrLf & _
                    "If This is Acceptable then Select - Yes to continue" & vbCrLf & vbCrLf & _
                    Space$(20) & "Otherwise" & vbCrLf & vbCrLf & _
                    "Select - No and return to the Dispensing / Labels Form" & Space$(6) & vbCrLf & _
                    Space$(5) & "and Select a Treatment"
            Response = MsgBox(strMsg, Style, Title)
            If Response = vbYes Then
                strSQL = "UPDATE tblDispensingPage SET tblDispensingPage.Verified = -1, tblDispensingPage.VerInitials =" & Chr(34) & txtNewInitials & Chr(34) & _
                    " WHERE (((tblDispensingPage.NWK)=" & Chr(34) & strNWK & Chr(34) & ")" & _
                    " AND ((tblDispensingPage.Cohort)=" & Chr(34) & strCohort & Chr(34) & ")" & _
                    " AND ((tblDispensingPage.Period)=" & Chr(34) & strPeriod & Chr(34) & ")" & _
                    " AND ((tblDispensingPage.DoseDay)=" & Chr(34) & strDoseDay & Chr(34) & "));"
                Exit Sub
            End If
    End If
        ' Verify Records based on selected SQL
        ' This next sections determines the number of records that are being updated.
        ' If there aren't any records it gives the user an error message and rolls back query
    Dim ws As Workspace
    Dim db As Database
    Set ws = Workspaces(0)
    Set db = CurrentDb
    db.Execute strSQL
    If db.RecordsAffected = 0 Then
        strMsg = "No Records were Modified?" & vbCrLf & vbCrLf & _
        "Please Verify that You've Selected" & vbCrLf & _
        "The appropriate Fields" & vbCrLf & vbCrLf & _
        "If you have and this Message is Repeated" & vbCrLf & _
        "Please contact your Database Administrator"
        Title = "Number of Records Affected"
    MsgBox strMsg, vbQuestion + vbOKOnly, Title
        ' Undo changes
    End If
    'DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True

Hmm, get the impression your records have (at least) two statusses:
1) Reviewed
2) Verified
This status is depending on the fields correctly filled, thus when entering (e.g. using the OnCurrent event) a record you can deduct the status by running the "verify check".
Basically a function could be used and called in the query to show the status and you can drop the field from the table all together...

Getting the idea ?




The User reviews the record to ensure that the correct data is entered into the fields. The verification is a visual that the User looked at the data. The VerifyAll doesn't verify any of the data (except to test for null), it only a quick way for the User to mark all of the records as Verified without checking off each record individually.

The field Verfied is a visual check for the data_Entry folks that the record has been reviewed and verified.

How should I place this process in a function???

I see, I would expect that the "Null" test is done when entering the data, otherwise the record can't be verified.
In case of the verification I would use a button [Verified] that's placing Now() in a DateVerified and Environ("username") in a VerifiedBy field. This way the process can be checked afterwards when and by who the verification has been done.

I would probably use a separate form for the verification and allow the user to "batch process" the records one by one on a separate form that will show all not yet verified rows. With a button they can verify and/or skip a row.
On this form you can disable the [Verify] button when Null fields are found in a row.
Looks to me that an additional comment field for the reason why skipped can come in handy...

For the batch prcessing you could use the mechanism from my sample database:
The code behind "Update all filtered records" gives an idea how to show only (user selected or by you the not verified rows) in a separate processing form.

Idea ?




First, Thanks for the database and suggestions, I will search that out...
Second, although the way I'm doing it isn't the cleanest method (I do at least understand it), as RockiRoad suggest, can I trap the error (# ?)  and backout of the Update and provide a message to the user so that they can prevent/correct the write conflict.

To be honest, I don't follow the process you last described. I would like to be able to do this cleaner, but I need to better understand your process.

The records are selected and verified by on the current need (i.e. Study / Cohort / Period / etc.), once verified the records are used to print labels. This a a error that I need to handle and I need to learn how to do it correctly. (I'll look at your database and see if I can grasp your solution).


Hmm, perhaps it would help when you envision cascading combo's for your filter fields on top of the datasheet subform.
When all fields have been selected you can apply the filter on the subform and use it for activating the button for the "batch processing form".
Looks to me that the selection will differ in time, depending on the selection made.
This would require a reset of all other rows and the marking of the last selection to enable the production of a specific label print session.
A solution could also be to use a sequence number for label batches to keep them separated.

Perhaps some more elaboration on the needs of the label production would give additional thoughts/improvements.



You have described the form that I use to filter the records.

User selects the Study Cohort etc, each selection (7 filters) making the filter more restrictive. This generates a query or sql that the form Dispensing uses to open the table filter to the specified records or the various Reports and Labels.

This is only an issue concerning Write Conflicts with the Dispensing form, and only if the User edits the current records, without saving it and then clicking on the VerifyAll button.

Hope this clears things up.

Having the filter will enable you to produce the labels, why mark the rows for processing with a Yes/No field instead of using the query "straigh away" ?



The data is processed as follows:

   First the data is entered by a tech,
   Second the data is verified by a supervisor
   Third the data is used to print the labels

I've look at your database and although there are some new ideas for this and future projects (if that's okay), it still doesn't resolve my original question:

Now that I understand what's happening better -

How to I trap the error or prevent the error from happening when the Supervisor makes a change to the records, but doesn't save the records and then clicks the VerifyAll which causes the write conflict. (Error 3197 - I think). The real issue is that when this occurs MicroSoft opens the warning screen which offers the supervisor choices on how to handle the conflict, if the Super 'Saves to Clipboard' or 'Drops Changes' then No Problem, if they 'Save Record' - results in Write Conflict, corrupts the records.

Thanks for your patience, Nico
Hmm, checked your cod and found it rather "heavy".
When you "code" your combo's with the value to filter and a "*" for all then just building one WHERE clause like:
                " WHERE (((tblDispensingPage.NWK) like " & Chr(34) & strNWK & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Cohort) like " & Chr(34) & strCohort & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Period) like " & Chr(34) & strPeriod & Chr(34) & ")" & _
                " AND ((tblDispensingPage.DoseDay) like " & Chr(34) & strDoseDay & Chr(34) & ")" & _
                " AND ((tblDispensingPage.Treatment) like " & Chr(34) & strTreatment & Chr(34) & "));"
will solve the many IF's.
Can't you make the subform read-only to prevent the update problem ?
Else issue first a me.subformname.form.requery before issuing the UPDATE query.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thank you for the light...
 I will review your filter code and implement it tomorrow (won't be back at work till then) and I would imagine that the .requery is the answer that I've been looking for...

I can't lock the form because the info may need to be edited by the Supervisor.

I let you know tomorrow morning, Thanks Imoutwest


Thanks Nico, Once again you provide the solution.

I added Me.Form.Requery as the first line in the On Click event of the VerifyAll command button. (Solved the Write Conflict) - Which brings up another issue to be resolved later: Did the User on clicking the VerifyAll want to Update the changed record????

And deleted 24 lines of code and consolidated my strSql into what you suggested and change the strTreatment as follows.

         strTreatment = IIf(Nz(Forms!frmfilter.Filter5.Value) = "<All>", "*", IIf(Nz(Forms!frmfilter.Filter5.Value) = vbNullString, "*", Forms!frmfilter.Filter5))

Thanks again for your help (all comments)


Glad I could help :-)

BTW for the "<All>", "*" in a combobox I normally use a query like:
select "*" as ID, "<All>" as Description from tblX
select ID, Description from tblX;

Now by making the first column hidden and use that as the bound column it will show the user "<All>" and let the code use the combo's value "*".

Success !

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.