Solved

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

Posted on 2006-10-30
18
741 Views
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?
0
Comment
Question by:Imoutwest
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 17836217
How do you fetch the record?
Is this an unbound or bound form?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17836272
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 ?

Nic;o)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17836344
1)
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?


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


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

0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17836418
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...
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17836447
>>Would it help to post the code?
It would
:-)
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17836495
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)
    Else
        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) & "));"
            Else
                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
   
    ws.BeginTrans
    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
        ws.Rollback
    Else
        ws.CommitTrans
    End If
           
    'DoCmd.RunSQL strSQL
        DoCmd.Requery
        DoCmd.SetWarnings True
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17836524
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 ?

Nic;o)
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17836624
Nic;o

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???
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17837116
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:
http://www.geocities.com/nico5038/Sample-Datasheet-Filter-ObjectAction-97.zip
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 ?

Nic;o)

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 7

Author Comment

by:Imoutwest
ID: 17837371
Nic;o

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

Imoutwest
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17837513
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.

Nic;o)
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17838100
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17842888
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" ?

Nic;o)
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17843570
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
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 17845113
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.

Nic;o)
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17846127
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
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 17854297
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)

Imoutwest
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17859101
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
UNION
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 !

Nic;o)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now