Failed Update, Jet engine thinks there are two updates happening

[jayde]

I am working with Access 2000. When a user is finished with a data entry form, I want to check to see if a field is populated correctly and fix it if it is not. When the user closes the form the field values are examined and if they aren't populated correctly I run the code below and get the following message:

The Microsoft Jet Database engine stopped because you and
another user are attempting to change the data at the same time.

Dim dbs As Database, rst As Recordset, IDSearch As Integer

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("NAMES&quot

IDSearch = Forms![frm_EditRecord]![MainID]


DoCmd.Close acForm, "frm_EditRecord", acSaveYes

rst.FindFirst "MainID = " & IDSearch
If rst.NoMatch Then
'leave records alone
Else
With rst
.MoveFirst
.Edit
rst!Displayorder = 1
.Update
.Close
End With
End If

dbs.Close

While running this code there is absolutely no one else using the database. I have no other processes occuring that are hitting the table I am working on. The table is a linked table to a SQL 7.0 backend.

Any thoughts?? This is making me crazy.

Thanks,

Jayde

 

[cmmrfrds]

It sounds like Access still has the initial record in edit mode. Did you tab off the record before closing the form to commit the update. What event are you in with your code? There is probably a DoCmd to update the initial record before you try to update a second time.

 

[jayde]

I started out executing this after a record update. In order to test I would make a small record change and press shift enter which would fire the event.

When I couldn't get that to work, I tried putting this code behind a button and just pushing the button without making recordchanges. I got the same message.

As you can see in the code above, I inserted a line in the code to close the form that was referencing the record after capturing the unique ID I needed to find the record. At the point the code was executing there were no other forms open that could have been referencing the record.
It was a Saturday with no one else logged onto the network. I tried restarting the SQL Server Services on the Server and rebooting the workstation I was working on. None of it made any difference.

Any other thougts??

 

[Mute101]

I think the order your code works is causing a problem.

close the form then open your recordset therefore there is no conflict in data.

ie.

Dim dbs As Database, rst As Recordset, IDSearch As Integer

    IDSearch = Forms![frm_EditRecord]![MainID]
          

    DoCmd.Close acForm, "frm_EditRecord", acSaveYes
Set dbs = CurrentDb
            
    Set rst = dbs.OpenRecordset("NAMES")
    rst.FindFirst "MainID = " & IDSearch
        If rst.NoMatch Then
                    'leave records alone
        Else
           With rst
                    .MoveFirst
                    .Edit
                     rst!Displayorder = 1
                    .Update
                    .Close
           End With
        End If

dbs.Close

As you are using SQL Server you should also get in the habit of destroying threads as you are finished with them, the rst.close isnt enough to do this you should also have a set rst = nothing.

 

[jayde]

Thanks, I'll try that....
Jayde